How to implement multi-level cascading slicers and filtering?

[JTsoi]

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 out, and the slicing will propogate all the way down to the main fact table.

I have 3 slicers at the moment: A report slicer, a facility slicer, and a category slicer. The facility and category slicer work as expected, and even have a measure on them to make slice each other if their values don't exist in the main table (taken from https://www.sqlbi.com/articles/syncing-slicers-in-power-bi/). 

My issue lies with getting the report slicer to work. The scenarios I'm trying to create are as thus:

• If I selected the Admin View Report, every row in the main table should appear (because no filters are defined for it).
• If I selected the Furniture Report, every row with a category code of "FUR" should appear.
• If I selected the Mamboville's Report. every row with a facility code of "MAM" should appear.
• If I selected the Saneville's Misc Report. every row with a facility code of "SAN" AND a category code of "MIS" should appear.
• If I selected the Service + Misc Report, every row with a category code of "MIS" or "SER" should appear.

At the moment, if I select any report, the filter only cascades down to the ReportCategoryFilter and ReportFacilityFilter table, and stops there. The things I've tried to do are as follows:

• Changing cross-filter directional to "Both" for ReportFacilityFilter <-> Facility and ReportCategoryFilter <-> Category. Doesn't work because then the filtering path from Report -> ... -> GL Entries becomes ambigous. But I need both relationships to apply!
• Merging all the values together into one table and creating all possible combinations of report + filter + facility, and filtering my main fact table with this new table of combined values. Works but the performance is horrible with my real data, because my real Power BI report actually has 20 "reports" and 3 slicers grouped together for reporting purposes, and combining them all together causes something like 20 reports * 30 * 705 * 340 = 143820000 total possible combinations. Toss on top of that around 4600000 rows from my GL Entry table joining to the combination table, and the filtering is going to absolute get wrecked.

I have the sample pbix file, but I'm not sure how to upload content, that'll take me a moment to figure out, I'll edit this post if I figure it out.

[Bmejia · Accepted solution]

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]

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]

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()

)

[JTsoi]

Was able to get a PBIX file into my OneDrive, here it is!
https://gscsca-my.sharepoint.com/:u:/g/personal/jtsoi_gscs_ca/EfXsIPJW04BBnfroiXiiv2gBRRumIwCNvKNg9V...