Forum Discussion
Parameter with hierarchy
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
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!
😁😁
1 Reply
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!
😁😁