Forum Discussion

captain_abhi's avatar
captain_abhi
New Member
2 years ago
Solved

Multi select slicer work as AND operation.

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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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