Forum Discussion
Slicer reverse selection in chart
Hi afaque03,
In your scenario, please create another table (Table2) contains the column Year value, assume the sample data like below:
Make sure there is no relationship between these two tables.
Then create a measure within Table2 like below:
Select Value = IF(HASONEVALUE(Table2[Year]),VALUES('Table2'[Year]),BLANK())
Create a measure in Table1 like below:
Total = CALCULATE(SUM(Table1[Sales]),FILTER('Table1','Table1'[Year]<>'Table2'[Select Value]))
Then place the Year column from Table2 in a slicer visual, Place measure 'Total' as line chart value. See:
Best Regards,
Qiuyun Yu
Another pattern I like to use to invert a selection uses the EXCEPT function (and can handle multiple selection):
=
CALCULATE (
[Your measure],
EXCEPT ( ALL ( YourTable[Year] ), VALUES ( YourTable[Year] ) )
)
- Hi, the syntax you have mentioned is what I was looking for :-) My requirement is to allow Multiple selections. Like, If I select year 2012 and 2013, the other visual should show data related to all years except 2012 and 2013. However, I am unable to interpret your syntax properly. Can you please elobrate it in detail; how to use this syntax to enable multiple reverse selections. Thanks.
Fantastic response OwenAuger. If this were my question I would ave given this the solution tag. I have applied this to multiple uses, such as Customer XYZ vs. All less Customer XYZ
Thank you!!
- Hi, OwenAuger shared a fantastic idea, however I was unfortunately unable to interpret it properly into use case. Please can you share this in detail (with some demo example of power BI) where Multiple Reverse selections are possible. Thanks.
