How to filter on comma separated column

[sa000]

Hi.

Suppose I have sample data as this

Product	Color
A	red
B	red
C	blue
D	red,blue
E	red,blue
F	blue
G	blue

I would like the user to be able to filter by the color, red OR blue, not "red, blue". So if the user selects Red, A,B,D,E are filtered. Right now I'm not sure how to create a filter to do that. 

I see a similiar post which looks like it accomplishes what I want but is a bit hard to follow, https://community.fabric.microsoft.com/t5/Developer/Filter-on-comma-separated-multiselect-field/m-p/...

[Ashish_Mathur]

Hi,

In the Query Editor, split the colour column by rows using the comma delimiter.  Click on Close and Apply.  Now create slicer/visuals.

[sa000]

This is great! Can you outline your steps so I can reproduce in my usecase?

[Ahmedx]

to know how to do this watch my video

https://1drv.ms/v/s!AiUZ0Ws7G26RjVdgrQApbiyC-hcE?e=Ncv57v

[sa000]

Thank you! This almost works except I can't do multi selection without an error? Is there a way to accomplish this

[Ahmedx]

pls try this code

[Ahmedx · Accepted solution]

pls try this

[ParameswaranP]

Hi,

Can you help me with a measure to count the No. of red and No. of blue?

Product	Color
A	red
B	red
C	blue
D	red,blue
E	red,blue
F	blue
G	blue

In this case, the output expected is Red = 4, Blue = 5

[ParameswaranP]

Hi Ahmedx,

Thanks for your reply. 
I'm using your concept based on 451.pbix, where there is a mesure "flag" is used in "Table", because I encoutered as error while displaying all similar to 448.
Now, with this "Count 2" measure in 'DimColor', I'm getting the same error when all the values are loaded.

Can you resolve this?

[Ahmedx]

Pls try

[ParameswaranP]

This works well for the scenario 

RedCount = CALCULATE( COUNTROWS('Table'), SEARCH("red", 'Table'[Color], 1, 0) > 0 )

[Ashish_Mathur]

Hi,

In Power Query, split the Color column into rows.  To your Table visual, drag Color to the row labels and write this measure

Measure = countrows(Data)

Hope this helps.

[ParameswaranP]

Thanks for your inputs Ashish.

I'm using 451.pbix as the base to develop this.

[Ashish_Mathur]

Please try the method i suggested in my previuos message.