Create a slicer to filter CSV column by individual values

[TChapa]

I have a column with CSV values. I want to make a slicer that can filter the data based on individual values in the CSV. 

If I drag the Options column into a slicer I get the Red slicer which shows all the distinct values. 

Instead I want to make one like the Green slicer with individual options.

Above is dummy data, but I'm trying to figure out the logic of how to do it. I created a static table with the 6 options that I used for the green slicer. Ideally I want to extract the individual values out of the CSV column programatically since new options may be added later. So far I've been only successful at getting the distinct values (similar to the red slicer) out of the column. Not really sure how to split it after that and use it as a filter.

In the red slicer if I select "Option 1, Option 4" it will only show me the first row containing both. However, with the green slicer I want it to show me all the rows that contain selected options like below. 

(I used a filter, but it's limited to only 2 conditions)

So Step 1: figure out how to extract individual options from the CSV.

Step 2: Use those values as filters on the table.

I'm very new to PBI and DAX so please provide as much detail as possible.

Thank you kindly!

EDIT: I already considered splitting the column into rows in Power Query, but it increases the amount of records which then change all the other visuals. The slicer then displays the correct individual values, but does not behave in the manner I'm describing above. 

[parry2k]

@TChapa I saw you updated the post, yes functionality after the split will not work. Split is required toward the solution, and on top of that model improvement and DAX are required to achieve the final result. It was never intended to work immediately after the column split. FYI

EDIT: I already considered splitting the column into rows in Power Query, but it increases the amount of records which then change all the other visuals. The slicer then displays the correct individual values, but does not behave in the manner I'm describing above. 

[parry2k]

@TChapa it is not a headache (somewhat), even if you have to do additive, splitting to the row will be required. Think this, there could be option 1, option 2, and in another case, it could be option 2, option 1, although it looks the same but different from the data point of view.

I know this additive solution can be done but if you don't have the option to split into rows then I'm not sure about the solution.

[TChapa]

For now I went with a manualy copying the distinct values of the column into it's own table. Then, like you said I split that into rows and used it as a filter. It gives me the slicer I want to see and as @CoreyP mentioned shows me "Option 1", "Option 2" AND "Option 1, Option 2" which I'll just say is better. I hope they don't add more options any time soon. Thanks for the help!

[CoreyP]

Another option for you would be to create a second slicer which has "Single Option" and "Multiple Options" values. Add a column with count of options. Then when someone clicks the Single Option button, the selection they make on the options slicer will only show the ones with one option. 

[parry2k]

@TChapa this is a data prep issue, and you have to rethink about how you want to make it work - and this is the right approach, otherwise, you will end up creating some complicated DAX measures to create the report. The choice is yours - I can only suggest what is best.

[TChapa]

I agree, the data isn't the best. It isn't mine and I have little control over it. That is why I'm looking for a backwards solution like the one I'm describing. I'm just trying to do the best I can with what I got which is why I'm looking for a solution to my specific use case even if it's creating some complicated DAX measures.

[CoreyP]

But what if a user wants to see rows that have only Option 1 and only Option 4? If you somehow achieve what you're wanting, users would lose this ability. 

[TChapa]

You bring up a good point. The requirement I was given was an additive filter. If multiple options are selected show only rows containing both. That's why this has been such a headache.

[parry2k]

@TChapa you need to split the options column value into rows:

click transform data -> select option columns -> arrow next to split column and then select "split column by delimiter" -> advance options -> Rows -> Ok 

[TChapa]

The problem with splitting it into rows is that it more than doubles the amount of records which throws off all the other visuals. Then if I try to select multiple options (say Option 1 & Option 4 like in the example) it shows me records with "Option 1" and records with "Option 4" AND "Option 1, Option 4" which is not what I want.

[CoreyP]

This post is similar and should help: https://community.fabric.microsoft.com/t5/Desktop/Single-Slicer-to-Filter-Multiple-Columns/m-p/34232...

[CoreyP]

Although, your use case is a little different. You'll probably just want to split your options column by delimiter, then unpivot the columns to rows.