cancel
Showing results for 
Search instead for 
Did you mean: 

Inverse Selector

Super User
1659 Views
Highlighted
Super User
Super User

Inverse Selector

This is a technique as well as a measure. The goal is to display two tables that represent both what is selected by a slicer and what is NOT selected by a slicer. To accomplish this, we create a disconnected table for our slicer using this table formula: 

 

Products = DISTINCT(Data[Product])

Make certain that there is NO relationship between this table and your data/fact table.

 

Next, we create the following two measures:

 

 

Standard Selector = 
VAR __dept = MAX([Department])
VAR __products = VALUES(Products[Product])
VAR __table = SELECTCOLUMNS(FILTER(ALL('Data'),[Product] IN __products),"__dept",[Department])
RETURN
IF(__dept IN __table,1,BLANK())

 

 

Inverse Selector = 
VAR __dept = MAX([Department])
VAR __products = VALUES(Products[Product])
VAR __table = SELECTCOLUMNS(FILTER(ALL('Data'),[Product] IN __products),"__dept",[Department])
RETURN
IF(__dept IN __table,BLANK(),1)

You can then add these selectors to your tables. If you want to hide these, use the "Column headers" format settings to turn off word wrap and then shrink the column until it is invisible. 

 

 

 

 


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

Proud to be a Datanaut!