cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
afaque03 Regular Visitor
Regular Visitor

Slicer reverse selection in chart

Hi Friends,

 

I have a double line chart and a year slicer with values 2012, 2013 and 2014 I want a reverse selection in this chart such that when I select 2012 the chart data should display the total amount for year 2013 and 2014 and when I select year 2013 the total sum to be of year 2012 and 2014 in two diffrent lines

screenshot-www.espressographics.com-2016-12-01-15-45-40.png

Just same as the above image. So basically i want a reverse selection of slicer

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-qiuyu-msft
Moderator

Re: 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:

 

Table1.PNGTable2.PNG

 

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:

 

w2.PNGw1.PNGw3.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 REPLIES 8
Moderator v-qiuyu-msft
Moderator

Re: 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:

 

Table1.PNGTable2.PNG

 

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:

 

w2.PNGw1.PNGw3.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
OwenAuger Super Contributor
Super Contributor

Re: Slicer reverse selection in chart

 

@afaque03@v-qiuyu-msft

 

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

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




afaque03 Regular Visitor
Regular Visitor

Re: Slicer reverse selection in chart

@v-qiuyu-msftThanx a lot it worked for the sum, Now I have one more case in that case that filter should also work for product name. As currently in this situation the DAX function you gave me works only for amount as it has sum() and calculate(). How can I list those products only which are selected in that slicer.

 

So in this situation when I click on year 2014 it should list all the data of 2013 and 2012 in line chart and there is one more chart which is a slicer so the slicer contains the product name. When I select an year from the year slicer for example 2012 than my product slicer should display all the products of 2012 and line chart to display the amount of 2013 and 2014. As in current situation the product name is from Table1 and year slicer is of Table2 so as there is no relationship between these two tables therefore when I click on year slicer of that of Table2 the line chart works fine but there is no changes in the product name list slicer. Can you suggest some DAX function for product name as well

jamesrr25 Frequent Visitor
Frequent Visitor

Re: Slicer reverse selection in chart

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

amnadeem1991 Regular Visitor
Regular Visitor

Re: Slicer reverse selection in chart

Hi, I just tried it and found your solution great! However, my requirement is to allow Multiple selections. Like, when I select more than one 'years' in the visual of table 2, it resets the visual of table 1 (means, only single selection works). Is there any way to enable multiple selections? If I select year 2012 and 2013, the other visual should show data related to all years except 2012 and 2013. Please share your idea. Thanks.
amnadeem1991 Regular Visitor
Regular Visitor

Re: Slicer reverse selection in chart

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.
amnadeem1991 Regular Visitor
Regular Visitor

Re: Slicer reverse selection in chart

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.
Highlighted
piyushmohnot Regular Visitor
Regular Visitor

Re: Slicer reverse selection in chart

Did it worked for anyone?

 

Thanks,

Piyush

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 357 members 4,411 guests
Please welcome our newest community members: