Multiple Hierarchies
Sometimes in Power BI you may want to build reports like the one in the screenshot below, where items are part of multiple hierarchies. You can see that items are categorized by color and by type within the same visual:
Multiple Hierarchies Power BI
In the below screenshot, you can see the model I have set up to solve this kind of scenario:
Multiple Hierarchies Power BI
The model makes use of many-to-many pattern and the table ItemsHierarchies is where the magic happens. Let us go through the contents of each table to make sense of it.
Customers, Calendar, Items, Sales, Hierarchies
These five tables are just simple dimension and fact tables which you find in most data models:
Customers Table
Items table
Calendar Table
Sales table
Hierarchies table
ItemsHierarchies
As anticipated earlier, in this bridge table is where the different levels and hierarchies are defined. If you are already familiar with Parent-Child hierarchies, this will look no stranger to you. If not, I suggest you read this interesting article. What I have added to the general pattern is the definition of a HierarchyID column which allows for the creation of Parent-Child paths within each HierarchyID only.
When first loaded into Power BI, the table looks like this, I have separated the rows belonging to a different hierarchy in order to make more visual sense:
Raw ItemsHierarchies
As you can see, different Parent Child relationships are defined within each HierarchyID. In order for the PATH function to work properly, we need to add two calculated columns which concatenate the HierarchyID with the ItemID and the Parent ID in order for each Item to have only one Parent.
The two calculated columns are:
1 2 3 4 5 6 7 8 9 | HierarchyItem = ItemsHierarchies[HierarchyID] & "." & ItemsHierarchies[ItemID] HierarchyParent = IF( ISBLANK( ItemsHierarchies[Parent ID] ), BLANK(), ItemsHierarchies[HierarchyID] & "." & ItemsHierarchies[Parent ID] ) |
Thanks to this we can now calculate the Path column:
1 | Path = PATH( ItemsHierarchies[HierarchyItem], ItemsHierarchies[HierarchyParent] ) |
And the Level1 and Level2 columns. If you more levels you would add more columns:
1 2 3 4 5 6 | Level1 = LOOKUPVALUE( ItemsHierarchies[ItemName], ItemsHierarchies[HierarchyItem], PATHITEM( ItemsHierarchies[Path], 1, TEXT ) ) |
1 2 3 4 5 6 7 8 9 10 | Level2 = IF( PATHLENGTH( ItemsHierarchies[Path] ) < 2, ItemsHierarchies[Level1], LOOKUPVALUE( ItemsHierarchies[ItemName], ItemsHierarchies[HierarchyItem], PATHITEM( ItemsHierarchies[Path], 2, TEXT ) ) ) |
Once the columns are added, the table will look like this:
Building the Visual
After creating a hierarchy containing Level1 and Level2, you can drop the Hierarchy Name column from the Hierarchies table in rows section of a matrix along with the hierarchy you have just created containing Level1 and Level2 and dropping this measure in the values section:
1 2 3 4 5 | Total Sales Quantity = CALCULATE( SUM( Sales[Amount] ), SUMMARIZE( ItemsHierarchies, Items[ItemID] ) ) |
It is worth noting that this visual can be fully sliced by Dates or Customers.
The dataset used in this example was trivial but following the same logic you could easily build something like a balance sheet matrix where each entry can be part of different hierarchies.
Books that have made a difference in my DAX journey: