Forum Discussion

jo123456's avatar
jo123456
Helper I
1 year ago
Solved

Dynamic Slicer

Hello,

 

I'm looking for an approach comparative to Tableau for dynamic slicer selection. 

 

Simplified: I have a slicer one with the following selections"Vegetables", "Fruits" & "Nuts"

 

I have a second slicer with apple, grapes, watermelon, orange, broccolli, cauliflower, peanuts, almonds & cashews. 

 

In tableau if a user selects Vegetables, it automatically picks up the first value alphabetically cauliflower,

if user selects fruits,  it automatically selects the first value of apples. 

 

Is there a way to force a similar behavior in Power BI, what I have now is if user selects vegetables then I select cauliflower then if user selects fruits then I need to deselect cauliflower to select a fruit for it to work.  Thank you

Need Help
Tips and Tricks
  • AmiraBedh's avatar
    AmiraBedh
    1 year ago

    Power BI does not have the built-in ability to automatically deselect previous slicer selections as Tableau does, so manual deselection will still be necessary in the second slicer unless you use visuals and dynamic measures to display the output based on user selection.

    You can create a mpping table that links the categories with the default item you'd like to select from the second slicer and and create a relationship between the first slicer’s table (Category) and the mapping table (Category).

    Then create a measure :

    SelectedItem = 
VAR SelectedCategory = SELECTEDVALUE(CategoryTable[Category])
RETURN
CALCULATE(
    FIRSTNONBLANK(MappingTable[Default Item], 1),
    MappingTable[Category] = SelectedCategory
)

     

    While this doesn't "force" the second slicer to automatically select items, it dynamically updates a visual with the first item corresponding to the category selection. Users can then interact with the second slicer as needed for finer control.

4 Replies

  • AmiraBedh's avatar
    AmiraBedh
    Super User
    1 year ago

    Power BI does not have the built-in ability to automatically deselect previous slicer selections as Tableau does, so manual deselection will still be necessary in the second slicer unless you use visuals and dynamic measures to display the output based on user selection.

    You can create a mpping table that links the categories with the default item you'd like to select from the second slicer and and create a relationship between the first slicer’s table (Category) and the mapping table (Category).

    Then create a measure :

    SelectedItem = 
VAR SelectedCategory = SELECTEDVALUE(CategoryTable[Category])
RETURN
CALCULATE(
    FIRSTNONBLANK(MappingTable[Default Item], 1),
    MappingTable[Category] = SelectedCategory
)

     

    While this doesn't "force" the second slicer to automatically select items, it dynamically updates a visual with the first item corresponding to the category selection. Users can then interact with the second slicer as needed for finer control.

  • divyed's avatar
    divyed
    Super User
    1 year ago

    Hello jo123456 ,

     

    There is no direct method but we can do some trick like below :

     

    1. Considering you have 2 tables , one for items with values like apple, grape,broccoli etc and another for item category with values like Fruits, Vegitables and Nuts

    2. Add a custom column Category in first table "Items" with below dax 

        

    Category =
    SWITCH(
        TRUE(),
        'Items'[Item] IN {"apple", "grapes", "watermelon", "orange"}, "Fruits",
        'Items'[Item] IN {"broccoli", "cauliflower"}, "Vegetables",
        'Items'[Item] IN {"peanuts", "almonds", "cashews"}, "Nuts",
        BLANK()
    )
     
    3. Create a measure called first_Selection using below dax, this will give first item for each category :
     //Measure to Pick the First Value Alphabetically
    First_Selection =
    VAR _Selection=
    CALCULATE(
        MIN(Items[Item]),  // Returns the first alphabetical value
        FILTER(
            Items,
            Items[Category] = SELECTEDVALUE(Category[Item_Category])  // Filter by selected category
        )
    )
    RETURN
    IF( ISFILTERED(Category[Item_Category]) =TRUE(),_Selection,"Please Select Item Category")
     
    4. Create a slicer for item_Category and add Item_catagory as values
    5. Add a table => First_Selection as value and do some formatting to remove borders,headers etc
    6. Make sure you have valid relationship on table items and Item_Category if you have used both the tables
    7. try clicking slicers, it will show a single value in the table which can be used as another slicer to filter other visuals as per need.
     
    It will look like below
     

     

     

     

    Power BI doesn't natively allow for slicer values to be automatically selected. However, with this measure (First Selection), you can display the default first value in a card or other visual, and guide users accordingly.

    Alternatively, you can set up custom bookmarks and sync slicers to automatically reset them when certain conditions are met (like changing the first slicer), though this is more of a manual process than an automatic selection based on a DAX formula.

     

    You can do formatting as per need. I hope this helps.

     

    Did I answer your query ? If Yes , please mark this as solution.

     

    Cheera