Combining many dynamic slicers into one slicer (via Field Parameters or otherwise)

[Greg1]

I have a large (c.10GB) data set in a fact table within Power BI. Each row in the fact table has about 20 categorical fields (eg showing the Product, Year, Smoker, Region etc) and each category has between 2 to 20 possible values (eg for Smoker:"Y" or "N", or for Year: 2018, 2019, 2020, 2021, 2022, 2023).

Current Approach:

Currently I use bookmarks in Power BI with predefined combinations of slicer selections. However, due to the large number of slicers, selections and the need for an audit-trail, I want to implement a Master Slicer where users can select a single "Filter Name" that automatically applies multiple slicer conditions based on predefined filter settings stored in a CSV file.

Desired Functionality

The Master Slicer should allow users to select a single filter option, which will automatically apply all the corresponding slicer selections.

The filter definitions will be stored in an external "Slicer Selections" CSV file (simplified example with just 4 categories below), specifying the slicer values for each Filter ID. 

It should also support:

• Wildcard selections (e.g., "All" instead of listing every possible value).
• Overlapping selections (e.g., the "East" region may appear in multiple filters).
• I would like the slicers to be applied to everything in the report, or at least at the visualisation-level. I don't want to have to put every single existing measure into a DAX formula that does the filtering for every individual category.
• Multiple selections within a single cell (e.g., Year: "2020, 2021, 2022" instead of separate rows for each year). However, I understand I could deal with this somehow when importing via PowerQuery.
• Ideally allow for new categories to be added in the future without significant developments   

Any help would be greatly appreciated (or letting me know if this just simply isn't possible!) 

Let me know if you need any more information on this in order to help. Thanks!

Simpilfied Example CSV of slicer selections:

Filter ID	Filter Name	Product	Year	Smoker	Region
1	XPC_Y_NE	XPC	All	Y	North, East
2	H87_Recent	H87	2020, 2021, 2022	All	All
3	All	All	All	All	All
4	All: 2020+	All	2020, 2021, 2022, 2023, 2024	All	All
5	All_N_East	All	All	N	East
6	All_N_North	All	All	N	North

[V-yubandi-msft]

Hi @Greg1 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[V-yubandi-msft]

Hi @Greg1 ,

We haven’t received a response yet and want to ensure the solution met your needs. If you need any further assistance, feel free to reach out we’d be happy to help. If everything is working as expected, kindly mark it as Accepted as solution.

Thank You.

[V-yubandi-msft]

Hi @Greg1 ,

I wanted to check if you had the opportunity to review the information provided @Deku . If it did, please consider marking the answer 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.

Thank you.

[Greg1]

Thanks very much, but is there a way do to this without having to manually create separate calculation items for each filter combination? 

I was really hoping for each filter combination to be driven directly off of a separate Master Table of filter combinations, so that all filters are shown in one central place for easy checking & updating, and so Power BI would automatically update if the Master Table was updated. 

I have tried using Field Parameters along with a filter mapping table for combinations of filters, which nearly worked, but failed to filter text columns correctly when there are multiple entries of the same text in the filter table (eg. having multiple filters with "East" in the "Region" column). This seems to be a limitation of the Field Parameters approach.

Is there a way of making the Field Parameters+Mapping table flexible enough to support my points above, or a different, fairly automated way?

Thanks!

[Deku]

You might struggle with the "All" parts. You could inject the values into the TREATAS. For the All would be fine if you had a list that represented all items but would be hard to maintain. You can't easily say if All then don't filter as IF is a bit funny about returning tables

[Deku · Accepted solution]

Calculation groups.

Each filter combo would be a separate calculation item.

I.e.

XPC_Y_NE =

Calculate(

Selected measure(),

Datum[products] = "XPC",

Datum[smoker] = "y",

Treatas( {"North","East"}, datum[Region])

)

If you add this in a slicer then all measures in visuals will be calculated within that filter context. You might want to wrap each filter in keepfilters if you want to intersect with user filter selections for those fields