Slicer with multiple select as contains in multiple values column where all selected values

[m2r]

Hello,

Here is what I am trying to do, below is the table I have:

Product	Characteristics
A	1
B	1;2
C	1;2;3
D	3
E	3;2
F	1;3
G	4
H	4;3;2
I	2;1
J	2

What I want is the slicer with unique Characteristics – 1,2,3,4

When I select 2, it should display B C E H

When I select 2 and 3, it should display C E H

When I select 2 and 3 and 4, it should display H

I am a beginner in PBI so I would really appreciate if you can provide steps or share a file. I tried searching the forums and multiple different methods but no success.

Thank you in advance for your help.

[Daniel29195 · Accepted solution]

@m2r 

output : 

steps 

step1 

go to power query -->  select table -->  select column characteristics -->  select split by columns under transform --> 

table result : 

now write the following measure : 

measure = 
var ctr =  COUNTROWS(ALLSELECTED('Table'[Characteristics]))

var dss = 
FILTER(
ADDCOLUMNS(
    VALUES('Table'[Product]),
    "@c", 
    CALCULATE(
        DISTINCTCOUNT('Table'[Characteristics]),
        ALLSELECTED('Table'[Characteristics])
    )
),
[@c] = ctr
)




return CALCULATE(COUNT('Table'[Characteristics]), KEEPFILTERS(dss))

drag product column and the measure to the visual : 

let me know if this works for you .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[vicky_]

The best way would be to fix your table - ideally, you would want a table like:

Product	Characteristics
A	1
B	1
B	2
C	1
C	2
C	3
...	etc

To acheive the above, go back into Power Query by clicking Transform Data. 

Then go to Transform > Split Column by Delimiter > ";" 

Lastly to get the correct format, select the Characteristics.1, Characteristics.2, etc. and go to Transform > Unpivot Table. Then remove the excess column. You can now load the table back into PowerBI.

Once there, you can simply put the Products in a table / matrix, and use the Characteristics Column in a Slicer.