Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Direct and Inverse Selector

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

1 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.

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

I created an Inverse Slicer Measure once...

 

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

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.

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.

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Greg,

 

It's working perfectly.

Thank you so much....

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.