Forum Discussion

afaque03's avatar
afaque03
Helper I
9 years ago
Solved

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 to...
Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    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

OwenAuger's avatar
OwenAuger
Super User
9 years ago

 

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

 

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    amnadeem1991
    Helper I
    8 years ago
    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.