Forum Discussion
Exclude selected slicer - different parameters
Hello!
I want to put a slicer that, when selected, it will exclude other data, not only what I have int the slicer.
To exclude only the selected data, I have this measure:
I want a measure that, when I select the event in the slicer, the month doesn't appears at all. For example: If I select the "Global" box, September should disappear completely, not being necessary to select the "act" box too to do it.
So basically: I want a slicer that I select one parameter and another parameter is excluded.
Is it possible? Thanks in advance
Hi Anonymous ,
There are some ways maybe help you.
One way to exclude data based on a slicer selection is to use the EXCEPT function in DAX to return the difference between two tables. For example, you can create a measure like this:
Selection Exclude Events = CALCULATE ( SUM ( Events[Value] ), EXCEPT ( ALL ( Events[Month] ), VALUES ( Events[Month] ) ) )This measure will sum up the values of the events that are not in the selected month.
Another way to exclude data based on a slicer selection is to use the ISFILTERED and NOT functions in DAX to check if a column is filtered and return the opposite value. For example, you can create a measure like this:
Selection Exclude Events = IF ( ISFILTERED ( Events[Month] ), NOT ( Events[Month] IN VALUES ( Events[Month] ) ), 1 )This measure will return 1 for the events that are not in the selected month, and 0 otherwise.
A third way to exclude data based on a slicer selection is to use the USERELATIONSHIP and ALLSELECTED functions in DAX to activate an inactive relationship and filter by the selected values. For example, you can create a measure like this:
Selection Exclude Events = CALCULATE ( SUM ( Events[Value] ), USERELATIONSHIP ( Events[Month], ExcludeEvents[Month] ), ALLSELECTED ( ExcludeEvents ) )This measure will sum up the values of the events that are related to the selected month in the ExcludeEvents table, which is a disconnected table with an inactive relationship with the Events table.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hello!
I want to put a slicer that, when selected, it will exclude other data, not only what I have int the slicer.
To exclude only the selected data, I have this measure:
Selection Exclude Events =SWITCH(TRUE(),ISFILTERED('Calendar'[Month]) && VALUES('Relevant Events'[Event Categorie]) in VALUES('Exclude Events'[Event Categorie]),0,1)The measure allows me to exclude the events that I select. But in my Dashboard I have a Line Chart that has the Events and the Months:I want a measure that, when I select the event in the slicer, the month doesn't appears at all. For example: If I select the "Global" box, September should disappear completely, not being necessary to select the "act" box too to do it.
So basically: I want a slicer that I select one parameter and another parameter is excluded.
Is it possible? Thanks in advanceHi Anonymous ,
There are some ways maybe help you.
One way to exclude data based on a slicer selection is to use the EXCEPT function in DAX to return the difference between two tables. For example, you can create a measure like this:
Selection Exclude Events = CALCULATE ( SUM ( Events[Value] ), EXCEPT ( ALL ( Events[Month] ), VALUES ( Events[Month] ) ) )This measure will sum up the values of the events that are not in the selected month.
Another way to exclude data based on a slicer selection is to use the ISFILTERED and NOT functions in DAX to check if a column is filtered and return the opposite value. For example, you can create a measure like this:
Selection Exclude Events = IF ( ISFILTERED ( Events[Month] ), NOT ( Events[Month] IN VALUES ( Events[Month] ) ), 1 )This measure will return 1 for the events that are not in the selected month, and 0 otherwise.
A third way to exclude data based on a slicer selection is to use the USERELATIONSHIP and ALLSELECTED functions in DAX to activate an inactive relationship and filter by the selected values. For example, you can create a measure like this:
Selection Exclude Events = CALCULATE ( SUM ( Events[Value] ), USERELATIONSHIP ( Events[Month], ExcludeEvents[Month] ), ALLSELECTED ( ExcludeEvents ) )This measure will sum up the values of the events that are related to the selected month in the ExcludeEvents table, which is a disconnected table with an inactive relationship with the Events table.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.