Forum Discussion
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...
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:
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
Hi PaulDBrown Thanks for your support.Could you pls tell me how you derived those exclude tables - from fact table/dimension table?
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'