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

Try:

Exclude =
VAR S1 = COUNTROWS (EXCEPT (VALUES(Table [Column1]), VALUES(Slicer1[Column1])))

VAR S2 = COUNTROWS (EXCEPT (VALUES(Table [Column2]), VALUES(Slicer2[Column2])))

VAR S3 = COUNTROWS (EXCEPT (VALUES(Table [Column3]), VALUES(Slicer3[Column3])))

RETURN

S1 + S2 + S3

 

add this measure to the filter for the visual in the filter pane and set the value to greater or equal to 1

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi pauld -Thanks for the reply.
I tried that but It's not working

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    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:

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi PaulDBrown Thank you so much for the reply.
      I think it works with single fact table-In my case I've multiple fact tables.
      Any Idea/hint for multiple fact tables?

       

      • 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

      Hi PaulDBrown  When I use exclude item in visual filter for card visual it's not working.
      Could you please help me with that?-That will be helpful for me to an extant.

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        4 years ago

        You need to create a new measure for the card visual. In my example:

        Sum of sales (Card Visual) =
CALCULATE (
    [Sum Sales],
    FILTER ( 'Dim Channel', [Exclude Dim Channel] >= 1 ),
    FILTER ( 'Dim Item', [Exclude Dim Item] >= 1 ),
    FILTER ( 'Dim REF', [Exclude Dim REF] >= 1 )
)