Forum Discussion

MinCK's avatar
MinCK
Frequent Visitor
2 years ago
Solved

Dynamic Slicer without Field Parameter

Hello Folks!

I am trying to create two slicers which filter the revenue bar graph I have. I want my first slicer to be category and filter the second slicer which is sub-category. For example, the first slicer comprises of category like state, area, hotel name, the second slicer will filter accordingly like California, New York, or Hilton, Marriot. I know it can be easily created by using Field Parameter, however I want to keep the connection through Direct Lake, which creating parameter isn't allowed (if not using Tabular Editor). 

 

I tried creating 2 slicers straight-forward way. However, my first slicer always shows a hierarchy allowing me to drill down to sub-category by itself, which I want it to show only the category and let me drill down whatever sub-category it is in the second slicer.

 

I just mover to power bi and in the learning process. Thank you experts in advance.

 

Best,

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,

    Thanks for the solutions Ritaf1983  and Sergii24 provided, and i want to offer some more information for user to refer to.

    hello MinCK , based on your descriotion, you can consider to to the following solution

    Sample data

    1.Create a category table

    Then create the following measures

    statevisible = IF(ISFILTERED('Category'[Categoty])&&1  in VALUES('Category'[Index]),1,0)
    areavisible = IF(ISFILTERED('Category'[Categoty])&& 2 in VALUES('Category'[Index]),1,0) 
    hotelvisible = IF(ISFILTERED('Category'[Categoty])&& 3 in VALUES('Category'[Index]),1,0)

    Then create three slicers, and put the measures to the visual filter.

     

     

     

    Output

     

     

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi,

    Thanks for the solutions Ritaf1983  and Sergii24 provided, and i want to offer some more information for user to refer to.

    hello MinCK , based on your descriotion, you can consider to to the following solution

    Sample data

    1.Create a category table

    Then create the following measures

    statevisible = IF(ISFILTERED('Category'[Categoty])&&1  in VALUES('Category'[Index]),1,0)
    areavisible = IF(ISFILTERED('Category'[Categoty])&& 2 in VALUES('Category'[Index]),1,0) 
    hotelvisible = IF(ISFILTERED('Category'[Categoty])&& 3 in VALUES('Category'[Index]),1,0)

    Then create three slicers, and put the measures to the visual filter.

     

     

     

    Output

     

     

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • Sergii24's avatar
    Sergii24
    Super User
    2 years ago

    Hi MinCK, as of my knowledge the desired output can be achieved by using field parameter or bookmark. In your case, I'd suggest to replace the first slicer (the one with category selection) with a bookmark selector: once a value is selected, you show the corresponding slicer for subcategory by hiding other subcategories (each as a separate slicer, visibility of each is managed through a bookmark).

    I hope it helps!   

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    Hi MinCK 
    The alternatives of using field parameters is the dynamic measures :

    https://www.youtube.com/watch?v=3RcRqp_xgAw

    Or bookmarks :

    https://www.youtube.com/watch?v=CFCN34GGPLQ

    Note that for the switch between category and sub-category, you don't need the slicers,
    you can get it with a hierarchical drill options:

    if you use the drill-down option ( not expend) this will switch categories/ sub category.

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly