Inverse Selector 03-19-2019 08:38 AM

[Greg_Deckler]

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. 

eyJrIjoiZGZiYTJiODMtN2I4MC00ZGI1LTg3MGQtMDJmMzgwMGY1OGQyIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9