Select with slicer, then inverse select from using another slicer

[Mattys31]

Hi all,

First post, so please be gentle.

I have a products table and a customers table. One to many join between customers and products, ie a single customer can have more than one product.

The field "level 1 product" is the highest level in the product hierarchy.

I have a disconnected table containing all the values of the level 1 product.

I would like to be able to select a product in a slicer based on Level 1 product in the main product table, to see how many customers have that product.

Then i want to be able to select a product from the disconnected table and show customers from the previous filter who DON'T have that product.

for example:

Products table

Customer Id | Product

1                   | Car

1                   | Boat

2                   | Car

2                   | Boat

2                   | House

3                   | Boat

disconected table created as dt = values(Product);

Slicers created for Products[Product] and dt[Product]

I want to filter the products table for customers with a car using the Products[Product] slicer, leaving me with 2 customers.

I would then want to use the dt[Product] filter to REMOVE customers who don't have a house, leaving only customer number 1 who has a car, but no house.

I have tried using calculate(DISTINCTCOUNT(Products[CUSTOMER ID]),EXCEPT(ALL(Products[Product]),VALUES(dt[Product])))

which shows me just customers who don't have a house in my example, as I'm using ALL.

I would then want to reapply the original filter to show customers who don't have a house, but do have a car.

Hope that makes some sense!

Any help would be gratefully received!

Thanks

Matt

[v-rongtiep-msft]

Hi  @Mattys31 ,

I have created a simple sample, please refer to it to see if it helps you.

Create a table with product column.

Then create 2 measures.

Measure = 
var _2=CALCULATE(MAX('product'[customer id]),FILTER(ALL('product'),'product'[product]=SELECTEDVALUE('Table'[product])&&'product'[customer id]=SELECTEDVALUE('product'[customer id])))
return _2

Measure 2 = var _2=CALCULATE(MAX('product'[customer id]),FILTER(ALL('product'),'product'[product]=SELECTEDVALUE('Table'[product])&&'product'[customer id]=SELECTEDVALUE('product'[customer id])))
return 
IF(_2=BLANK(),1,0)

Then filter the measure 2 is 1.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

[Mattys31]

Hi Polly,

This seems like it's almost there but i need 2 slicers, 1 to select a product, then another to exclude a product from customers who have the product selected in the first slicer. So in your example, selecting boat shows only customers without a boat, which is correct, but I'd like to see customers who DO have a boat but dont have a house. In my example, I would want to choose customers with a car on one slicer, meaning customer numbers 1 and 2, and then filter that result for customers who don't have a house by selecting house from another slicer, which would return only customer 1.

So in steps,

1) select car from slicer 1

Returns customers 1 and 2.

2) Select house from slicer 2

Filters the rows returned from slicer 1 and REMOVES customers who don't have the selection in slicer 2, so returns customer 1 only.

Hope that's clearer!

Thanks

Matt

[v-rongtiep-msft · Accepted solution]

Hi @Mattys31 ,

Please add a measure.

And another table without relationships with other tables.

slicer 2 = VALUES('product'[product])

Measure 3 = var _1=SELECTEDVALUE('slicer 2'[product])
var _2=CALCULATE(MAX('product'[customer id]),FILTER(ALL('product'),[Measure]<>BLANK()&&'product'[product]=_1&&'product'[customer id]=SELECTEDVALUE('product'[customer id])))
return
IF(_2=BLANK()&&[Measure]<>BLANK(),1,0)

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

[Mattys31]

This is great, Thanks Polly...