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!
Enjoy!
Denny Lee 
Hi Denny,
You had me going there for a while 🙂 I suppose I’ve never tried adding a hierarchy as you’ve shown here, so it took a few moments to realize what was going on.
The way I get around the hierarchy problem is to create a named set. The named set hierarchy can then be dragged to the PivotTable. Although this works, it means working in MDX(the static sets that Excel creates aren’t terribly useful). But… the whole point of DAX is to avoid MDX! Perhaps in V2, DAX could be extended to support named sets, eh?
Absolutely agreed, but Rob had rightly noted that most people wanted to avoid the MDX method of doing it hence why I started with this 🙂
Saying this, I am hoping that we solve this for V2 as well too 🙂
[…] To continue reading, click here. […]
I guess in this instance I would have created a calculated column in the mapCityState table in PowerPivot once the relationship was defined as =RELATED(mapState[StateName]). Then you would have the two fields in the same table and you would only need to make this one visible to the end-users and they would know that they are in fact related. Otherwise a user might not be aware that the two tables/fields are related to each other. You still don’t get the navigation/drilldown path like in SSAS with user defined hierarchies.
Makes sense to me Dan – great idea to call otu the RELATED function, eh?!
[…] saw one of Denny Lee’s tweets this morning titled “Hierarchies, Oh Hierarchies…where are thou? (in PowerPivot)”, so I was intrigued since I just talked about this not being available yesterday in my PASSMN […]
Hi Denny,
I just installed PowerPivot (SQL Server 2012) and was faced exactly the same problem. Although it should have worked with the relations created in PowerPivot, it didn’t. Unfortunately, your workaround doesn’t work as well 😦 Seems that all relations are lost when creating a pivot table out of powerpivot. Does anybody has an idea?
Using the related function should work, but there must be a way without workaround, or?