Filtering on multiple columns using one slicer

Power BI method for filtering on multiple columns with one field using only relationships and no DAX.

I’m rebuilding my main report in Power BI. This was the report I created when I first started learning to use Power BI so became quite a mess as I learned to do new things and added in new data sources. I’m rebuilding this from scratch so is a good time make some posts about the pitfalls I’m avoiding and some new techniques I’ve implemented.

The Request

The users want to be able to select a name from a slicer to view only information relating to that person’s sites.

The Challenge

I have a fact table that contains financial results by site. I have an estate map that gives further details for the site, including names of people who are related to that site. The Contract Director, the Regional Director, the Regional Manager, the Management Accountant, etc.

Each of these is a column so I need to be able to filter on any one of these columns using a single slicer. A person could also appear in multiple columns so I need to be able to show a site if the person’s name appears in any column.

The Solution

I could use filtering within my DAX measures to do this but I’d rather not add any complexity to these if I can help it. Using relationships to filter these feels more “natural”.

So how to we go about it?

I’ve made a little sample fact table…

Sample Fact

…and a sample estate map.

Sample EM

There’s a one to many relationship between the Estate Map and our fact table.

The next step is to add in a column that concatenates the names against each site. I’ve done this in the query builder in M using the Text.Combine function.

=Text.Combine({[First Column],[Second Column],...},";")

Concat Column

Added Concat

Next I need a table with a unique list of these concatenated names. First create a new blank query and simply use the Table.Column function to pull out the Concat column from the Estate Map table.

=Table.Column(#"Estate Map","Concat")

This will create a list and in the Transform tab under List Tools select Remove Duplicates then To Table. Leave the options as the default.

The next table I need is a list of names and a list of the concatenated combinations they appear in. I’ve called this query Interim Step.

Interim Step

The are a couple of different ways you can get to this table but the way I decided to do it at the time starts in exactly the same way as the previous query so you can right click on the query and select Duplicate or Reference. Either will work.

Next I duplicated the column using Duplicate Column under the Add Column tab. Then I split one of the columns by using Split Column -> By Delimiter in the Transform tab. The table looks pretty familiar at this point.

Interim 2

Next select the delimited columns and use Unpivot Columns in the Transform tab and then delete the Attribute column it creates as we don’t need it.

[EDIT: I’ve found a better way of doing this step. When you do Split Column, in the dialogue box select Advanced options then select Rows. This basically does the split column and transpose step in one go.]

Interim 3

The final table needed is a unique list of names. This uses the same Table.Column formula as the first table, except you pull out the Person column from the Interim Step table we just made.

=Table.Column(#"Interim Step","Person")

Again we remove duplicates and convert to table.

Now we have all the tables we need. We just need to build the relationships between them.

Relationships

As the Interim Step table contains no unique values the default relationship between it and the Concat List flows the wrong way. You’ll need to make this relationship function in both directions otherwise the whole thing won’t work.

Use the Person column in the Person List table in your slicer and it will flow all the way through to your data. The Interim Step and Concat List tables can be hidden as they won’t be needed when building a report.

Let me know what you think in the comments or if you have a better solution.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s