Show Separate Values for Each Selection in a Power BI Slicer for a Multi-Select Field
Hello, Power BI lovers! I wanted to share a recent discovery I made that helps bring Power BI slicers to a new level of elegance. A frustration I had previously was that for environment on which Enterprise Custom Fields allowed for multiple selections, I could not get a slicer to show each selection individually. Instead, it would show values EXACTLY as they appeared in the fields. Not so elegant, as you can see:
What I instead wanted to show is each value separately: my name, Scott’s name, and Kiran’s name on the Slicer. Well, there’s a way to do that!
In Query Editor, go to the field that contains multiple values.
Right click on the Column heading, and select Split column, by Delimiter:
Although Comma is an available delimiter, we actually want a Custom Delimiter so that we can ensure inclusion of the space that will exist between entries as part of what Power BI sees as the Delimiter, otherwise our split rows will result in some leading spaces. Below, I have my custom delimiter set as “, “ (with a space after the comma).
The result is your column will look like this:
And then your slicer and data will look like this:
Now keep in mind, as you can see above, rows will be duplicated in a Table visualization, so you may prefer using a Matrix, and either removing that Requestor field, or handling it another way. More importantly, you’ll need to ensure your table relationships are appropriately set to One-to-Many where applicable.
Good luck, and happy reporting!