Forum Discussion

JTsoi's avatar
JTsoi
Frequent Visitor
11 months ago
Solved

How to implement multi-level cascading slicers and filtering?

I'm trying to implement a slicer that basically slices other slicers. The goal is for a user to pick one slicer, and in doing so, multiple other slicers will have their choices automatically sliced o...
Need Help
  • Bmejia's avatar
    Bmejia
    11 months ago

    Hi JTsoi,

    I didn't get to see your uploaded file until after put the attached file.  


    1st you want to create one column on your table in the GL Entrie table as I have in the "YourTable" for each logic.

     


    2nd Create a table with all your report filters like i have in the V_table

     

    3rd. you want to create a measure like the one in the file "Breakout Filter"

    Breakout Filter =

    VAR selectedCategory = SELECTEDVALUE( V_Table[FilterCat])
    VAR FUR =CALCULATE( SELECTEDVALUE(YourTable[Furniture]),YourTable[Furniture]>0)
    VAR ADM =CALCULATE( SELECTEDVALUE(YourTable[Admin]),YourTable[Admin]>0)
    VAR MAM =CALCULATE( SELECTEDVALUE(YourTable[Mambovilles]),YourTable[Mambovilles]>0)
    VAR SAN =CALCULATE( SELECTEDVALUE(YourTable[Sanevilles Misc]),YourTable[Sanevilles Misc]>0)
    VAR SER =CALCULATE( SELECTEDVALUE(YourTable[Service + Mis]),YourTable[Service + Mis]>0)
    RETURN
    SWITCH(TRUE(),
    selectedCategory="Admin",ADM,
    selectedCategory="Furniture",FUR,
    selectedCategory="Mambovilles",MAM,
    selectedCategory="Sanevilles Misc",SAN,
    selectedCategory="Service +Mis",SER,
    "OTHER"
    )
     
    4th you want to add your report filter category

     


    5th you want to add the measure to your table but only in the filter pane not in the table and filter by "Show item when the value "is" equal to 1.

     



    Then you can tested


Bmejia's avatar
Bmejia
Super User
11 months ago

Hi JTsoi,

I didn't get to see your uploaded file until after put the attached file.  


1st you want to create one column on your table in the GL Entrie table as I have in the "YourTable" for each logic.

 


2nd Create a table with all your report filters like i have in the V_table

 

3rd. you want to create a measure like the one in the file "Breakout Filter"

Breakout Filter =

VAR selectedCategory = SELECTEDVALUE( V_Table[FilterCat])
VAR FUR =CALCULATE( SELECTEDVALUE(YourTable[Furniture]),YourTable[Furniture]>0)
VAR ADM =CALCULATE( SELECTEDVALUE(YourTable[Admin]),YourTable[Admin]>0)
VAR MAM =CALCULATE( SELECTEDVALUE(YourTable[Mambovilles]),YourTable[Mambovilles]>0)
VAR SAN =CALCULATE( SELECTEDVALUE(YourTable[Sanevilles Misc]),YourTable[Sanevilles Misc]>0)
VAR SER =CALCULATE( SELECTEDVALUE(YourTable[Service + Mis]),YourTable[Service + Mis]>0)
RETURN
SWITCH(TRUE(),
selectedCategory="Admin",ADM,
selectedCategory="Furniture",FUR,
selectedCategory="Mambovilles",MAM,
selectedCategory="Sanevilles Misc",SAN,
selectedCategory="Service +Mis",SER,
"OTHER"
)
 
4th you want to add your report filter category

 


5th you want to add the measure to your table but only in the filter pane not in the table and filter by "Show item when the value "is" equal to 1.

 



Then you can tested


  • JTsoi's avatar
    JTsoi
    Frequent Visitor
    11 months ago

    Hi Bmejia,

     

    This solution works for me, after I made changes to each of the individual columns to calculate/filter the rows I wanted to see. I'm not super excited about having to make 20 columns for the 20 reports, but I'm fairly lucky that I don't think I have to update this report with new filters after I publish it.

     

    Thanks again,

    Jonathan T.

    • Bmejia's avatar
      Bmejia
      Super User
      11 months ago

      You can also create measures, but still have to do one for each.  I don't think you can get away from not doing each individually.  either with a measure or column. 

      Example:  

      FUR = CALCULATE([MAM],FILTER(YourTable,[MAM]>0))
      MAM = CALCULATE(COUNTA(YourTable[Category Code]), YourTable[Category Code]="Fur")
       
      Breakout Filter2 =

      VAR selectedCategory = SELECTEDVALUE( V_Table[FilterCat])
      VAR FUR =CALCULATE([MAM],FILTER(YourTable,[MAM]>0))
      VAR MAM =CALCULATE( [Mam], FILTER(YourTable,[MAM]>0))
      RETURN
      SWITCH(TRUE(),
      selectedCategory="Furniture",FUR,
      selectedCategory="Mambovilles",MAM,
      BLANK()
      )