Dynamic hierarchy presentation



Have you ever been asked or wondered if it's possible to dynamically present a hierarchy level in a visualization based on a selected value in a slicer in Power BI?

I have created a solution for this, which can be used in various visualizations and can also be adapted for other scenarios with some modifications.

Let's start with a bit of context. My fellow Microsoft BI developers who have been working with Microsoft BI products for a while may still remember the MDX function "currentmember.level.ordinal" in MOLAP cubes. This function allows us to determine the level in a hierarchy of the value selected in a filter. With this information it's then possible to calculate a measure differently or dynamically present a different level from the hierarchy in a visualization using expression functions in SSRS.

Unfortunately, for us Power BI enthusiasts, we don't have a similar function in DAX. For this reason, I took the functional aspects of MDX as a starting point to see what is possible in DAX in combination with the data model. The necessary components to retrieve are:

  • Which level is the filter at?
  •  Which level do I want to present?
  • Level data for each hierarchy level.

Ultimately, I set myself three goals:

  1. Present children of the selected value in the slicer.
  2. Also present the selected value from the slicer along with its children, sorted so that the parent appears before the children.
  3. Show the parent in a different color than the children when using a bar chart.

To achieve the above, I had to create several DAX measures, expand the data model, and apply a visual level filter. At the end of this blog post, you will find a demo Power BI report where you can navigate and see how it is built and how it works. You can also download the PBIX file.

Below, I will mention and explain the DAX functions that were created and applied.

Measures:
What is the selected hierarchy level in my filter?

Selected Product Level = SWITCH(
TRUE(),
ISFILTERED('Product'[ProductName]), 4,
ISFILTERED('Product'[ProductSubCategoryName]), 3,
ISFILTERED('Product'[ProductCategoryName]),2,
ISFILTERED('Product'[AllProductsName]),1,
0
)

In this DAX formula, I check bottom-up which item from the hierarchy is being filtered. Then I return a level value from it. We do this bottom-up because otherwise, the first evaluation always evaluates to true.

What is the level that I want to present based on my filter selection?

Presented Product Level = SWITCH(

TRUE(),
ISFILTERED('Product'[ProductName]), 5,
ISFILTERED('Product'[ProductSubCategoryName]), 4,
ISFILTERED('Product'[ProductCategoryName]),3,
ISFILTERED('Product'[AllProductsName]),2,
1
)

In this DAX formula, I actually check the same thing as when determining the selected level. However, the outcome is 1 higher, indicating the level from the hierarchy that I want to see in the visualization.
The above measures serve as a verification to use on my canvas.

This is the moment when I want to compare the outcome of the second measure with a level of the hierarchy that I want to present in my visualization. I should mention that I have a table where my hierarchy is spread across columns. So, the granularity in my table is level 4.

For this reason, I have created an additional table where I have a record for the lowest granularity, linked to each level of my hierarchy, so my level 4 is repeated multiple times. In this table, I have added a column with a level notation. I extracted this table from the original dimension table using DAX.

Model expansion:
Dynamic Product Hierarchy =
VAR DPH1 = SUMMARIZE('Product','Product'[ProductKey],'Product'[ProductLabel],'Product'[ProductName],"Level",4)
VAR DPH2 = SUMMARIZE('Product','Product'[ProductKey],'Product'[ProductSubcategoryLabel],'Product'[ProductSubCategoryName],"Level",3)
VAR DPH3 = SUMMARIZE('Product','Product'[ProductKey],'Product'[ProductCategoryLabel],'Product'[ProductCategoryName],"Level",2)
VAR DPH4 = SUMMARIZE('Product','Product'[ProductKey],'Product'[AllProductsLabel],'Product'[AllProductsName],"Level",1)
RETURN
UNION(DPH1,DPH2,DPH3,DPH4)

Then, I established a relationship between the dimension table and this new table using the key column from the dimension and the first column of the new table which holds the same key. This relationship is set to perform bidirectional filtering. We will use this table in our visuals. In this demo, we place column 2 on the X-axis of our visualizations or on rows in the table visualization. We use the "level" column in our filter DAX formulas.

Now, I have all the components to create measures that can be used as filter options for my visualizations.

Measures:
The following measure provides a filter that only returns the children of the selected item in my slicer.
Filter - Dynamic Product = VAR SelectedLevel = SWITCH(
TRUE(),
ISFILTERED('Product'[ProductName]), 5,
ISFILTERED('Product'[ProductSubCategoryName]), 4,
ISFILTERED('Product'[ProductCategoryName]),3,
ISFILTERED('Product'[ALLProductsName]),2,
1
)
RETURN
IF(
SelectedLevel = SELECTEDVALUE('Dynamic Product Hierarchy'[Level])
,1,BLANK()
)

In this DAX formula, I will again determine the filtered hierarchy level in my slicer, but now I will use a value from my switch statement for the level I want to present. Then, I compare this outcome with the "level" column from my newly created table using the DAX function SelectedValue. If they are equal, I evaluate to 1 otherwise evaluate to Blank. I will use this outcome as a filter on my visual level filters.

Now, let's say that we not only want to see the children in the visual but also the parent. In that case, we will use the following DAX for our visual filter.
Filter - Dynamic Product Incl. Parent = VAR SelectedLevel = SWITCH(
TRUE(),
ISFILTERED('Product'[ProductName]), 5,
ISFILTERED('Product'[ProductSubCategoryName]), 4,
ISFILTERED('Product'[ProductCategoryName]),3,
ISFILTERED('Product'[AllProductsName]),2,
1
)
RETURN
IF(
SelectedLevel = SELECTEDVALUE('Dynamic Product Hierarchy'[Level])
||
SelectedLevel - 1 = SELECTEDVALUE('Dynamic Product Hierarchy'[Level])
,1,BLANK()
)

We can see that in the Return statement, I perform an OR evaluation so it will also evaluate to 1 on the parent level.

Now, let's say I only want to see the selected value from my slicer. In that case, we can apply the following DAX formula.
Filter - Dynamic Product Parent = VAR SelectedLevel = SWITCH(
TRUE(),
ISFILTERED('Product'[ProductName]), 5,
ISFILTERED('Product'[ProductSubCategoryName]), 4,
ISFILTERED('Product'[ProductCategoryName]),3,
ISFILTERED('Product'[AllProductsName]),2,
1
)
RETURN
IF(
SelectedLevel - 1 = SELECTEDVALUE('Dynamic Product Hierarchy'[Level])
,1,BLANK()
)

Lastly, we have a DAX formula for dynamically coloring our bar chart.
Parent Color = VAR SelectedLevel = SWITCH(
TRUE(),
ISFILTERED('Product'[ProductName]), 4,
ISFILTERED('Product'[ProductSubCategoryName]), 3,
ISFILTERED('Product'[ProductCategoryName]),2,
ISFILTERED('Product'[AllProductsName]),1,
0
)
RETURN
IF(
SelectedLevel = SELECTEDVALUE('Dynamic Product Hierarchy'[Level])
,"#7D4F73" /* Purple */ , "#B6960B" /* Gold */
)

I have now shared everything with you. It's up to you to play around with it and perhaps even expand it further. As mentioned before, below is a live report for you to explore. It also showcases the mentioned DAX formulas. Feel free to click on the icons within the pages, and on the second page, you can explore the data model by clicking on the model, the model disappears so you can continue your journey.

Additionally, there is a link to the live video I did for GetResponsive Live on Thursday, May 18 2023. The video is in Dutch, so my Dutch friends can also watch this for extra information and explanation.

Thank you for your interest in this solution. Follow me on LinkedIn for more exciting solutions in the future.




Hierarchy Level Ordinal.pbix
Download here a copy of the Power BI file on this page.
Hierarchy Ordinal selection.zip (36.53MB)
Hierarchy Level Ordinal.pbix
Download here a copy of the Power BI file on this page.
Hierarchy Ordinal selection.zip (36.53MB)