Forum Discussion
Exclude Slicer Selection
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:
Hi pauld -Thanks for the reply.
I tried that but It's not working
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:
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?You can create the measures referencing the dimension tables instead of the fact table and use the dimension table fields in the visuals
PaulDBrown Thanks-but when I tried with dimension table the logic was not working
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.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 ) )