cancel
Showing results for 
Search instead for 
Did you mean: 

Filtering and slicing either a multivalued column or multiple columns, a different approach!

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.

PART 1

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’.

image.pngTable_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.

image.pngNote the Index column that we added, and the four MultiList columns rather than just the one.

PART 2

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.

image.pngTable_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’.

image.png

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.

image.pngThe slicer based on 'Table_Mapping' [Value.1] and the Table visual with all columns from Table_Originalimage.pngThe slicer at work!

I hope this will help people out that are struggling with this without adding (expensive) DAX to your data model! 

 

Kind regards

Djerro123