Slicing overlapping subgroups of data with a synthetic “Filter Table”

I saw a post from Marco Russo (Twitter) showing a trick to use a slicer to filter across multiple columns at once in Power BI with some DAX and a new dynamic filter table. I’ve done something similar in the past to use a slicer not to slice data directly, but to change a view in the data or change filtering criteria indirectly – I wanted to share an example here to show a slightly different use case than Marco’s for a similar technique.

I want to create a slicer that switches the dataset between different subsets of data, but with overlap – like just my sales, sales for my group, and sales for the company. This is normally accomplished with a calculated column for the group the data is in, along with a slicer where if you want to see everybody, you have to select all three values like this:

But what if you want to create slicer options that aren’t exclusive? Where selecting “My Department” includes you too? Here’s how you do it!

In my example, I’ll need a fake sales and person table, so I’ll just use the “Enter Data” interface to create them:

Creating a sales table
Creating a user table (just a list from the sales table)

At this point, we could just join them on name and add some slicers, but here’s where we get more advanced by creating the filter table. The names repeat, but they show once for each slicer option they belong with, so whatever you select, you’ll see the relevant people:

Creating the filter table with all the slicer options in it

Once you have those three tables, you need to link them up – note the two-way relationship between the filter table and the person table. Without that, filtering on the last table won’t flow through to the sales table and our slicer won’t work:

Create the relationship and make sure the filter-user link to bi-directional

That’s all it takes – now you can create a slicer on the “Filter” column in the “Team Filter” table, and you’ll get whatever non-exclusive subset of the data you’re looking for!

I’m looking at only Bob’s sales
I’ve added Sally’s sales, but can still see Bob
Now I’m looking at everybody!

I made the slicer single-select in this example, which I do to avoid confusion – while this still works if you leave it a normal multi-select, it can lead to some confusion since users will expect the options to be exclusive and if they’re already got “Everybody” selected, neither of the other two options will do anything 🙂

If you want it, grab the PBIX file here.

I hope this is helpful to somebody – if you have any suggestions or other use cases for this, please add them below!

Leave a Reply

Your email address will not be published. Required fields are marked *