Forum Discussion

Manish1198's avatar
Manish1198
Helper I
1 year ago
Solved

Dynamic Slicer Filtering Based on Another Slicer Selection

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

 

Need Help
  • OwenAuger's avatar
    OwenAuger
    1 year ago

    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?

     

     

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    1 year ago

    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?