Add products a customer did not buy to list of all the products where there are sales

[LamSar]

Good afternoon,

A report users asked if it would be possible to also add the product a customer did not buy in the list (per year and month and client).
The model is as follows:

The report is like this:

 At the moment only the articles that a customer has bought are in the list, but now they also want to see, the articles a customer did not buy (with sales amount zero off course). Any ideas on how I could do this?

Thanks.

[v-jingzhang · Accepted solution]

Hi @LamSar 

You may consider using the EXCEPT function. EXCEPT function (DAX) - DAX | Microsoft Docs

EXCEPT(<table_expression1>, <table_expression2>

Get the full list of products as expression1 and the list of products bought as expression2. Then you can get a list of products that only exist in the expression1, which are those not bought by customers. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@LamSar , Put product and customer into a visual and assume you measure [purchase]

try a measure like

if(isblank([purchase]), 1,blank())

or

countx(addcolumns(summarize(Purchase, customer[Customer], product[product]) , "_1", if(isblank([purchase]), [Customer],blank()) ) ,[_1])

[LamSar]

Unfortunately this will not work for me.