Parameter with hierarchy

[fjjpeeters1976]

Hi all,

I have a matrix that shows costs over months by cost item and further drill down to cost account level. Now I want to build in a drill through that when they click on a cost they can go to another page they can see either which entity the costs are in or which function/subfunction the cost are in. This option I wanted to create with a parameter where the user could choose which view to see. However I see that the parameter is not allowing me to switch between two items that are hierarchies. Any suggestions how to tackle this issue?

kind regards,

Frank

[freginier · Accepted solution]

Hey Frank!

Since Power BI parameters do not support hierarchical switching directly, you can use a disconnected table and dynamic measures to achieve the effect.

First create a parameter table in Modeling - > new table , then add this DAX: 

HierarchySelection =
DATATABLE(
"View Option", STRING,
{
{"Entity-Level Costs"},
{"Function/Subfunction Costs"}
}
)

This table allows users to select which hierarchy they want to see.

Now, define a measure that switches between hierarchies based on the selected parameter.

SelectedHierarchy = SELECTEDVALUE(HierarchySelection[View Option])

DynamicHierarchy =
SWITCH(
[SelectedHierarchy],
"Entity-Level Costs", MAX(YourTable[EntityCost]),
"Function/Subfunction Costs", MAX(YourTable[FunctionCost]),
BLANK()
)

• Add the HierarchySelection[View Option] field to a slicer.
• This lets users choose between Entity-Level Costs or Function/Subfunction Costs.
• Replace your matrix values with DynamicHierarchy.
• Now, when users select an option in the slicer, the matrix updates to show either Entity Costs or Function Costs.
• Create a drill-through page.
• Add a drill-through filter with YourTable[Cost Item].
• Users can right-click on a cost item → Drill through → See the selected view.

Hope this helps!

😁😁