I had a great question from a customer concerning something weird happening with PowerPivot relationships. In this example, we have two tables such as city and state.
State Mapping Table
City-State Mapping Table
With the City-State mapping table, it is apparent that the cities of Boston, Quincy, Norwood belong to MA while Seattle and Redmond belong to WA. Even though the relationship makes sense (as per below)
the output doesn’t!
I wish we had hierarchies!
Alas, one of the biggest wishes in PowerPivot was that we had in hierarchies and we just were not able to put them in. But don’t be too harsh on us – we had to get all that other cool stuff in, eh?!
So what can we do to work around this issue? In data warehousing parlance, create a fact! What I mean here is that create a table that you would use to perform the count measurements such as a sum of sales figures such as the figure below.
Sales fact Table
Once you add this table and then ultimately, your relationship will be between the above two mapping tables and the above fact table. Once you do that then you’re good to go as shown in the figure below.
Hope this helps!