How should data model be set up?

[Editor7]

I have three files:
- Sites
- NAICS
- Interests

All are tied together by the SiteID.

Sites functions as the lookup table based on the SiteID as there are no duplicate records.
Both NAICS and Interests have multiple records for a SiteID.

I need to create a report with information from both NAICS and Interests. I can get one or the other, but not both together in the same PowerPivot table. How should I set this up to be able to report on all fields in these tables in one PowerPivot table?

 

[Jacob Barnett]

If you create the relationships from each of 'NAICS' and 'Interests' to 'Sites' respectively then in a Pivot where you have used a 'dimension' from the 'Site' table then you can easily drag in information from both the other two tables.

If this doesn't help you may need to share a workbook.

Jacob

 

[Editor7]

Let me answer my own question. I can use measures from the two tables (NAICS and Interests) that are linked to the lookup table (Sites), but I can't use dimensions from the linked tables. To use a dimension from one of the linked tables, I need to turn it into a measure by using HASONEVALUE, for example.

 

[Editor7]

Thanks, Jacob. I was running into a problem when I was trying to use attributes from the linked tables. Only amounts/measures can be used from the linked tables in the tabular model. At least, that is what I found with my sample data.

 

[Laurent C]

Attributes from "facts tables" can be used in row or column.

Do you mean, you encounter an issue with, say, attributes from your NAICS table not filtering attributes from your Interests tables?

In that case, you can either adapt your measures, or (best practise) create lookup tables for these attributes and define relationships accordingly.

EDIT:
A more precise (and accurate) answer would require more information on your attributes and what you want to achieve.