Slice table with AND instead of OR

[Bian]

Simple question but can't get my head around the solution.

1 Product Table

1 Customer Table

1 CustomerProduct Table

I have a slicer on Product and shows a table on the customers. I wan't to be able to select 2 (or more) products in the slicer and only show the customers that has all selected products.

Could you please point me in the right direction?

/Bian

[v-shex-msft · Accepted solution]

HI @Bian,

Slicer not support to change its filter logic. In my opinion, I'd like to suggest you create new table as source of slicer, then write a measure to compare selected value and table value and return result tag.

After these steps, you can drag above measure to visuals' 'visual level filter' to achieve 'and' filter effect.(notice: selector table not have relationship to original table)

Sample formula:

Select Tag =
VAR _selected =
    ALLSELECTED ( Selector[Item] )
VAR _current =
    CALCULATETABLE ( VALUES ( 'Sample'[Item] ), VALUES ( 'Sample'[Type] ) )
RETURN
    IF (
        CONCATENATEX ( INTERSECT ( _current, _selected ), [Item], "," )
            = CONCATENATEX ( _selected, [Item], "," ),
        "Y",
        "N"
    )

Result:

Regards,

Xiaoxin Sheng

[Bian]

Thank you very much for this. Will try to implement it and get back to you.

[Bian]

Works perfectly! Thank you very much.