Livio/ November 11, 2018/ DAX, Power BI/ 0 comments

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

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

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:

Thanks to this we can now calculate the Path column:

 

And the Level1 and Level2 columns. If you more levels you would add more columns:

 

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:

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.

Download the file

 

Books that have made a difference in my DAX journey:

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*