Return all values NOT related to sliced value

[jaliu1290]

Hello Power BI community!

I have a table called Dataset:

Location	Product
Toronto	ABC
Toronto	DEF
Toronto	HIJ
Calgary	ABC
Calgary	DEF
Vancouver	HIJ

On a page, I would like to insert a slicer by Location and return 2 tables:

Table 1 should return all Product related to sliced Location.

Table 2 should return entire list of Product, EXCEPT Product that is related to sliced location.

So for example, if I slice by Calgary:
Table 1 should return ABC, DEF
Table 2 should return HIJ

If I slice by Toronto:

Table 1 should return ABC, DEF, HIJ
Table 2 should return (blank)

If I slice by Vancouver:

Table 1 should return HIJ
Table 2 should return ABC, DEF

How do I create Table 2??

Thank you in advance!

[Kedar_Pande]

@jaliu1290 

Create Table 1 Measure

ProductsInLocation = 
CALCULATETABLE(
VALUES(Dataset[Product]),
ALL(Dataset),
TREATAS(VALUES(Dataset[Location]), Dataset[Location])
)

Create Table 2 Measure

ProductsNotInLocation = 
EXCEPT(
VALUES(Dataset[Product]),
[ProductsInLocation]
)

This setup ensures that Table 1 shows the sliced products, while Table 2 shows all other products.

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[v-denglli-msft · Accepted solution]

Thanks for the reply from MattAllington and rohit1991, please allow me to provide another insight.
Hi @jaliu1290 ,

Please refers to the following steps.
Create a calculated table that derives from the product field.

Create the following measure to flag unrelated products.

Measure = 
IF (
    SELECTEDVALUE ( 'Table'[Location] ) <> BLANK (),
    CALCULATE (
        COUNTROWS ( 'Product' ),
        KEEPFILTERS ( NOT 'Product'[Product] IN VALUES ( 'Table'[Product] ) )
    ),
    1
)

Use the calculated table field to create a table visual. Then add the measure to the visual level filter with the setting is 1. The final result is as follows.

Please see the attached pbix file for reference.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rohit1991]

To achieve this:

1. Table 1: Use default filtering; add a slicer for Location and display Product. It will show products related to the selected location.

2. Table 2: Create a calculated table with this DAX:
   Table2 =EXCEPT(VALUES(Dataset[Product]),CALCULATETABLE(VALUES(Dataset[Product]),Dataset[Location] IN VALUES(Dataset[Location])))

[MattAllington]

I wrote an article about this a few years ago. 
https://exceleratorbi.com.au/items-not-selected-slicer/