PowerBI "contains" slicer

Question

I have a table with a column that is a semi-colon delimited list of tags for each row. I would like to create a slicer that allows me to do a "contains" type slice on the tags column.

Example record:

Title       | Tags
foobar      | thingA; thingB
barfoo      | thingC; thingA; otherThing

Slicer

thingA |  thingB | thingC  

Selecting "thingA" should result in both rows being displayed.

I can see how to make a new query to populate the values of the slicer with a distinct action, but now how to create a relationship back to the original table to allow me to slice.

Answer 1

One way to approach this is to use a 2nd unpivoted version of your records table.

Step 1: Create an Unpivoted Records Table

You can unpivot your tags using the Query Editor.

1. Split Column by Delimiter on the Transform toolbar (specifically, split the tags column by a delimiter of ";").
2. Select the multiple Tags columns that result, and choose Format > Trim to get rid of any superfluous leading or trailing spaces that could mess things up.
3. Select the multiple Tags columns, and choose Unpivot Columns.

The result should look something like this:

Title  | Tag
-------|-----------
foobar | thingA
foobar | thingB
barfoo | thingC
barfoo | thingA
barfoo | otherThing

As long as you reach the final result, it doesn't matter how you get there.

Note, this table is in addition to the original tables in your question - it is not a replacement.

Step 2: Relate Your Tables

Your Unpivoted_Records table should have a relationship to your Slicer table (Single/Many-to-One on Tag) and also to your original Records table (Many-to-One on Title).

The magic ingredient is that the relationship between Unpivoted_Records and Records should have a cross-filter direction of Both (double-click on the line to change it).

Both is not the default. What this means is that any filters applied to Unpivoted_Records are propagated backwards to the original Records table, filtering that table too.

Diagram of how your relationships should look: notice the circled area, indicating the cross-filter direction is "Both"

Step 3: Slice Away

At this point, you can show the Records table in a visual, along with the Slicer table. When you select a value on the slicer, it will filter the Records table. You do not need to display the unpivoted Records table anywhere, as the slicer selections filter up to your original Records table.

Note on Cross-Filter Directions

A cross-filter relationship of both can have side-effects that I can't anticipate depending on your actual data model. It shouldn't in this particular example, since Records_Unpivoted only exists for the sole purpose of facilitating the relationship between the slicer table and the Records table. However, if you use a Both relationship in other places and notice oddness going on with slicing & filtering, try turning the relationship back to single to see if the cross-filter direction is the root cause.