Dynamic Slicer Filtering Based on Another Slicer Selection

[Manish1198]

Hi everyone,

I'm trying to implement a dynamic slicer filtering behavior. All the columns(DVP, VP Field Sales, RVP, RSD) are in the same table.

I want to create three slicers:

1. Hierarchy Slicer: This slicer will have the options "Ops" and "Sales".
2. DVP/VP Field Sales Slicer:
   When "Hierarchy" is "Ops": This slicer should display the distinct values from the DVP column of my "SalesData" table.
   When "Hierarchy" is "Sales": This slicer should display the distinct values from the VP Field Sales column of my "SalesData" table.
3. RVP/RSD Slicer: 
   When "Hierarchy" is "Ops": This slicer should display the distinct values from the RVP column of my "SalesData" table.
   When "Hierarchy" is "Sales": This slicer should display the distinct values from the RSD column of my "SalesData" table.

In essence, the "Hierarchy" slicer acts as a master control, determining which columns are used to populate the other two slicers.

Any help would be greatly appreciated! Thanks

[OwenAuger · Accepted solution]

Hi @Manish1198 

I would recommend using a modified field parameter table for this. I have attached a small PBIX example.

The steps I followed:

1. Create a field parameter table using the Power BI interface, containing the columns DVP, VP Field Sales, RVP, RSD.

The DAX expression for the table will appear like this:

Slicer Parameter = 
{
    ( "DVP", NAMEOF ( Data[DVP] ), 0 ),
    ( "VP Field Sales", NAMEOF ( Data[VP Field Sales] ), 1 ),
    ( "RVP", NAMEOF ( Data[RVP] ), 2 ),
    ( "RSD", NAMEOF ( Data[RSD] ), 3 )
}

2. Modify the table expression to include columns to classify each option as "Ops" or "Sales", and which slicer it should appear on ("DVP/VP Field Sales" or "RVP/RSD"):

Slicer Parameter = 
{
    ( "DVP", NAMEOF ( Data[DVP] ), 0, "Ops", "DVP/VP Field Sales" ),
    ( "VP Field Sales", NAMEOF ( Data[VP Field Sales] ), 1, "Sales", "DVP/VP Field Sales" ),
    ( "RVP", NAMEOF ( Data[RVP] ), 2, "Ops", "RVP/RSD" ),
    ( "RSD", NAMEOF ( Data[RSD] ), 3, "Sales", "RVP/RSD" )
}

The field parameter table appears as follows (with suitably renamed columns):

3. Create the slicers as below.

For the 2nd & 3rd slicers, ensure that you

• Choose "Show values of selected field" in the field context menu (if not selected by default)
• Apply a visual level filter on the field parameter's Slicer column corresponding to the slicer itself.

The fields on the 2nd & 3rd slicers then change when Hierarchy selection is changed.

Does something like this work for you?

[Manish1198]

Yeah, this definately works. Thanks