Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Exclude Slicer Selection

Hi, In my report there are 3 slicers-let it be Slicer1,Slicer2,Slicer3. If I select values those should be excluded from the Visual/Report(All the values from three slicers). Thanks in Advance. a...
Tips and Tricks
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    Ok, I have managed to test this.

    Create a measure for to exclude each slicer selection. In this example:

    Exclude Channel =
IF (
    ISFILTERED ( 'Exc Channel'[Channel] ),
    COUNTROWS (
        EXCEPT ( VALUES ( FactTable[Channel] ), VALUES ( 'Exc Channel'[Channel] ) )
    ),
    1
)

    Exclude Item =
IF (
    ISFILTERED ( 'Exc Item'[Item] ),
    COUNTROWS ( EXCEPT ( VALUES ( FactTable[Item] ), VALUES ( 'Exc Item'[Item] ) ) ),
    1
)

    Exclude REF =
IF (
    ISFILTERED ( 'Exc REF'[REF] ),
    COUNTROWS ( EXCEPT ( VALUES ( FactTable[REF] ), VALUES ( 'Exc REF'[REF] ) ) ),
    1
)

    Add these measures to the filters for the visual in the filter pane and set the value to greater or equal to 1:

    to get:

     

PaulDBrown's avatar
PaulDBrown
Community Champion
4 years ago

You can create the measures referencing the dimension tables instead of the fact table and use the dimension table fields in the visuals

Anonymous's avatar
Anonymous
Not applicable
4 years ago

 PaulDBrown Thanks-but when I tried with dimension table the logic was not working

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    Using my previous example, these measures work with dimension tables:

    The model

    Exclude Dim Channel =
IF (
    ISFILTERED ( 'Exc Channel'[Channel] ),
    COUNTROWS (
        EXCEPT ( VALUES ( 'Dim Channel'[dChannel] ), VALUES ( 'Exc Channel'[Channel] ) )
    ),
    1
)

    Exclude Dim Item =
IF (
    ISFILTERED ( 'Exc Item'[Item] ),
    COUNTROWS (
        EXCEPT ( VALUES ( 'Dim Item'[dItem] ), VALUES ( 'Exc Item'[Item] ) )
    ),
    1
)

    Exclude Dim REF =
IF (
    ISFILTERED ( 'Exc REF'[REF] ),
    COUNTROWS ( EXCEPT ( VALUES ( 'Dim REF'[dREF] ), VALUES ( 'Exc REF'[REF] ) ) ),
    1
)

    The visual is created with the fields from the dimension tables

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi PaulDBrown Thanks for your support.Could you pls tell me how you derived those exclude tables - from fact table/dimension table? 

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    You can make a copy of the dimension tables (as long as they contain the unique values covering whole range of values in the model. You can create them by selecting "New table" under modeling and:

    Excluded table = 'Dimension table'