Recently, I ran into a case where the customer required a filter on a multivalued column. However, he wanted to be able to slice based on a single value rather than the combinations of values. The solution we implemented was leveraging Power Query rather than DAX and enabled the customer to slice the data based on any occurrence in the multivalued column. This approach also makes it possible to filter in multiple columns using a single slicer! Note that an implementation in DAX is provided by this community blog. Part 1 of this post will cover the steps to take when dealing with a multivalued column. You can skip to Part 2 if you want to filter in multiple columns with one slicer.
Our data model is simple in this case, it only consists out of a multivalued column (MultiList) and a random value column (Value). The query is aptly named ‘Table_Original’.
Table_Original - Our original table with a multivalued column MultiList
The first thing we add to this table is an Index column. We will need that later on to create the relationship with it. Then, we duplicate this query and rename the query to ‘Table_Mapping’. Here we are going to extract every single value from the MultiList column. Go to the Transform ribbon in Power Query Editor and choose Split Column by Delimiter.
Note the Index column that we added, and the four MultiList columns rather than just the one.
If you skipped Part 1 and are looking for a way to filter in multiple columns using 1 slicer, just make sure you add an Index column to your query in the Power Query Editor. The columns we want to filter in the example are MultiList.1, MultiList.2, MultiList.3 and MultiList.4 but can be any columns you have in your table.
We can select all new columns (MultiList.1, MultiList.2, etc.) and hit the Unpivot Columns. This reshapes our query into new rows for every column we unpivoted. Now, we only need to delete the newly created column Attribute and the original Value column. The latter is not required anymore in this query.
Table_Mapping - We deleted all unnecessary columns and are only interested in Index and corresponding Value.1 column
Hit Save and Apply to return to Power BI Desktop and go to Manage Relationships. We want to create a one-to-many relationship between ‘Table_Original’[Index] and ‘Table_Mapping’[Index]. Power BI Desktop is intelligent enough to automatically create this for us. However, a slight adjustment needs to be made to the relationship in order for a slicer to work the correct way. The adjustment is setting the Cross Filter Direction to ‘Both’.
Time to build some visuals to see if this works! Create a slicer based on ‘Table_Mapping’[Value.1]. Create another Table visual with all columns from Table_Original. You can now select single values and the Table visual will display the corresponding rows.
The slicer based on 'Table_Mapping' [Value.1] and the Table visual with all columns from Table_OriginalThe slicer at work!
I hope this will help people out that are struggling with this without adding (expensive) DAX to your data model!