Direct and Inverse Selector

[Anonymous]

Hello,

I have a table like this:

Department     Product     Category

Sports                 AA                00001

Education            BB                00002

Health                 AB                00003

Sports                 AA                00004

Health                 BB                00005

I need a slicer for Product that will act as both direct and inverse slicer.

Eg:- If I select Product 'BB' in slicer -> It should display 2 tables.

1st  : Department and Category that HAS product BB

2nd : Department and Category that DOES NOT HAVE product BB

Is there a way to acheive this?

Thanks

[Greg_Deckler]

I created an Inverse Slicer Measure once...

https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Aggregator/m-p/342266

[Anonymous]

Hi Greg,

Thanks for your response.
I had a look at this before, it calculates the SUM.

But, I want to display the contents of the table based on the filter.

[Greg_Deckler · Accepted solution]

Right, so create a disconnected table like this:

Table6 = ALL('Table5'[Product])

Use this for your slicer. Then, create these two measures and use them in your tables. 

Normal Measure = 
VAR __dept = MAX([Department])
VAR __product = MAX('Table6'[Product])
VAR __table1 = SELECTCOLUMNS(FILTER(ALL('Table5'),[Product] = __product),"__dept",[Department])
RETURN
IF(__dept IN __table1,1,BLANK())


Inverse Measure = 
VAR __dept = MAX([Department])
VAR __product = MAX('Table6'[Product])
VAR __table1 = SELECTCOLUMNS(FILTER(ALL('Table5'),[Product] = __product),"__dept",[Department])
RETURN
IF(__dept IN __table1,BLANK(),1)

See Table5, Table6 and Page 3 of attached.

[Anonymous]

Greg,

It's working perfectly.

Thank you so much....

[Greg_Deckler]

I think I'll submit this as a new Quick Measure!