Dynamic Legends based hierarchy selection

Hey there!

Here’s how you can achieve that using DAX measures and potentially slicers or selection controls in Power BI:

Create a Dynamic Measure for Line Chart: You'll need a dynamic DAX measure that changes its behavior based on the selected level from the matrix. For example, you can use a measure like this:

DynamicLevelMeasure =
SWITCH(
TRUE(),
SELECTEDVALUE('Hierarchy'[Level]) = 1, SUM('Data'[Level1Amount]),
SELECTEDVALUE('Hierarchy'[Level]) = 2, SUM('Data'[Level2Amount]),
SELECTEDVALUE('Hierarchy'[Level]) = 3, SUM('Data'[Level3Amount]),
SELECTEDVALUE('Hierarchy'[Level]) = 4, SUM('Data'[Level4Amount])
)

You can add a slicer or matrix to allow users to select which level they want to display. The slicer will control which level the line chart visual shows.

Create a line chart and add the DynamicLevelMeasure as the value.

For the x-axis, you can use a time-based or sequential field (e.g., a date or period column).

Ensure that the slicer (or matrix) that controls the level is properly interacting with the line chart. You can adjust the interactions via the Format pane in Power BI to control how selections in the matrix or slicer affect the line chart.

Hope this helps!

😁😁

Hi Ravi_G

Power BI doesn’t let you directly switch the legend column based on a slicer, which makes dynamic legends tricky. But there’s a workaround that’s worked well for me.

What I usually do is create a separate slicer table (not related to the model) that just holds the level names  like “Level 1”, “Level 2”, “Level 3”. Based on what’s selected there, you can build a calculated column or measure that switches between the actual columns in your hierarchy.

Legend Label =
SWITCH(
    SELECTEDVALUE(LevelSelector[Level]),
    "Level 1", Table[Level2],
    "Level 2", Table[Level3],
    "Level 3", Table[Level4]
)

Then you use this Legend Label as the legend in your chart. It’s not truly dynamic in the sense of changing the metadata of the chart, but it gets the job done visually.

Just be aware that this works best when your hierarchy is flattened (i.e. columns for each level). If you're using a parent-child hierarchy, it’ll need a slightly different setup.