Exclude Slicer Selection

[Bharat7]

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.

@amitchandak @Greg_Deckler @AlexisOlson 

[Bharat7]

Thanks @amitchandak - I am using Independent slicers only. It is fine with one slicer but when I was trying with 2 or 3 slicers it's not working

[PaulDBrown]

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

[Bharat7]

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

[PaulDBrown · Accepted solution]

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:

[Bharat7]

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]

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 )
)

[Bharat7]

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]

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

[Bharat7]

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

[PaulDBrown]

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

[Bharat7]

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

[PaulDBrown]

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'

[amitchandak]

@Bharat7 , to exclude, it is better to have an independent slicer, I discussed here

Need of an Independent Table in Power BI: https://youtu.be/lOEW-YUrAbE

[Bharat7]

Thanks @amitchandak - I am using Independent slicers only. It is fine with one slicer but when I am trying with 2 or 3 slicers it's not working