Find products that customer did not buy

[Laila92]

I have three tables:
1. A product table

2. A sales table

3. A customer table

(see here for data https://docs.google.com/spreadsheets/d/1v0ONqPxPZdvVKtocFyvhG-NSJy3rPHXDGkCea_T6hzE/edit#gid=8994835... )

I am trying to achieve:
Show which products a customer did NOT buy.  With the dax below I did manage to make a table doing exactly that, but I am not managing to establish a relationship between the product and sales table. Only between the product and customer table. How can have the sales data and the data from the EXCEPT table in one table?

Total Org Pot. MRR =
var all_products = CROSSJOIN(
CALCULATETABLE(
DISTINCT(Deals[Organization ID]),
FILTER(deals,Deals[Customer Status]="Won"),
FILTER(deals,Deals[Status]<>"Open")
),
FILTER(
DISTINCT(Deals_Product_Lookup[Product Id]),
Deals_Product_Lookup[Product Id]=2||Deals_Product_Lookup[Product Id]=3||Deals_Product_Lookup[Product Id]=16||Deals_Product_Lookup
[Product Id]=6||Deals_Product_Lookup[Product Id]=8))
var products_won_orgs = SUMMARIZE(FILTER(deals,Deals[Status]="Won"),Deals[Organization ID],Deals[Product Id])

RETURN

EXCEPT(all_products,products_won_orgs)

[v-xuding-msft]

Hi @Laila92 ,

I'm not clear about your formula. Do you want a table? Does "Deals" mean "sales table" and "Deals_Product_Lookup" mean "product table"? If so, there is a "product id" column and you could create a relationship between the table and "sales table". I don't know what your actual tables look like.

If you want a measure, you could refrence this to have a try.

Measure =
CALCULATE (
    MAX ( 'product table'[Product Id] ),
    FILTER (
        'product table',
        NOT ( 'product table'[Product Id] IN ALLSELECTED ( 'sales table'[Product Id] ) )
    )
)

If this can't help you, please share your expected output. We will understand more clearly.

[amitchandak]

@Laila92 , Try to create a sales measure with +0

and then filter on 0.

[Laila92]

@amitchandak a measure calculating what?