Multi select slicer work as AND operation.

[captain_abhi]

Hi guys one query
 
states | categories
MH | water
MH | food
MH | IT
AP | IT
AP | food
KA | water
TS | IT
 
 
 i have one table in Power BI desktop in which i added a multi select slicer (water , food, IT ) and in default case if is select water and food then states will come like (MH ,MH , AP ,KA) , here Or operation is happed between multiselected value like water or food. But in my case I needed like water and food and output should be (MH, MH ) because MH is only state having both water and food.
 
How to do this

[Anonymous · Accepted solution]

Hi @captain_abhi 

You can refer to the following solution.

Sample data 

Slicer table

Create the following measures.

Conbine = CONCATENATEX(FILTER(ALLSELECTED('Table'),[States] in VALUES('Table'[States])),[Categories],",")

Flag =
VAR _count =
    COUNTROWS (
        FILTER (
            VALUES ( SlicerTable[Categories] ),
            CONTAINSSTRING ( [Conbine], [Categories] )
        )
    )
RETURN
    IF (
        ISFILTERED ( SlicerTable[Categories] ),
        IF (
            _count = COUNTROWS ( VALUES ( SlicerTable[Categories] ) )
                && SELECTEDVALUE ( 'Table'[Categories] ) IN VALUES ( SlicerTable[Categories] ),
            1,
            0
        ),
        1
    )

Then put the flag measure to the visual filter.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly