Parameter with hierarchy

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!

😁😁