Slicer Magic

[badger123]

Is there a clever way for me to create a slicer that works in an inverse manner, sort of like exclusions... Slicer has the items in (items 1, 2 and 3) and visual has the phrases. If I select item 2 in slicer, I would like the table visual to show only phrase 2, because phrases 1 and 3 are related to item 2. 

Backend table:

long_phrase	related_item
phrase 1	item 1
phrase 1	item 2
phrase 2	item 1
phrase 3	item 1
phrase 3	item 2
phrase 3	item 3

Desired output based on above example, table visual:

long_phrase

phrase 2

Thanks in advance!

[v-yulgu-msft · Accepted solution]

Hi @badger123 ,

New a calculated table which is unlinked to source table 'Table3'. Add [related_item] from this table into slicer.

slicer table = VALUES(Table3[related_item])

Create below measures, add it to visual level filter and set its value to 1.

Measure_1 =
VAR _slicerselection =
    SELECTEDVALUE ( 'slicer table'[related_item] )
VAR _itemlist =
    CONCATENATEX (
        FILTER (
            ALLSELECTED ( Table3 ),
            Table3[long_phrase] = SELECTEDVALUE ( Table3[long_phrase] )
        ),
        Table3[related_item],
        ","
    )
RETURN
    IF ( ISERROR ( FIND ( _slicerselection, _itemlist ) ), 1, 0 )

Best regards,

Yuliana Gu