Forum Discussion

badger123's avatar
badger123
Resolver I
7 years ago
Solved

Slicer Magic

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_phraserelated_item
phrase 1item 1
phrase 1item 2
phrase 2item 1
phrase 3item 1
phrase 3item 2
phrase 3item 3

 

Desired output based on above example, table visual:

long_phrase
phrase 2

 

 

Thanks in advance!

Need Help
Tips and Tricks
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    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