Forum Discussion
WillT
Community Admin
10 years agoCustomers who bought multiple products
Hi everyone. Worked on a little problem recently to try and figure out "customers who bought combinations of products". I have seen plenty of posts and great patterns on Basket Analysis (e.g. DAX Pat...
OwenAuger
Super User
10 years agoHI there,
Here are a few options I came up with :)
The last one (v4) seems to run fastest but please test at your end with actual data.
- On your existing measure, you can get rid of the ISBLANK test and just add zero (since Blank + Zero = Zero):
Buyers of All Selected Products v2 = COUNTROWS ( FILTER ( SUMMARIZE ( Sales, Sales[Customer], "ProductsBought", DISTINCTCOUNT ( Sales[Product] ) ), [ProductsBought] = COUNTROWS ( VALUES ( Sales[Product] ) ) ) ) + 0 - Here is an alternative using EXCEPT on two lists of products to see if removing the selected products from each customer's list leaves an empty list (rather than comparing product counts):
Buyers of All Selected Products v3 = COUNTROWS ( FILTER ( VALUES ( Sales[Customer] ), ISEMPTY ( EXCEPT ( VALUES ( Sales[Product] ), CALCULATETABLE ( VALUES ( Sales[Product] ) ) ) ) ) ) + 0 - This version is a bit convoluted but seems to run fastest. The SUMMARIZE(GENERATE(...)) part returns a list of customers who didn't buy all selected products, then the outer EXCEPT takes the difference between the full customer list and this list, leaving customers who did buy all selected products.
Buyers of All Selected Products v4 = COUNTROWS ( EXCEPT ( VALUES ( Sales[Customer] ), SUMMARIZE ( GENERATE ( VALUES ( Sales[Customer] ), EXCEPT ( VALUES ( Sales[Product] ), CALCULATETABLE ( VALUES ( Sales[Product] ) ) ) ), Sales[Customer] ) ) ) + 0
All the best,
Owen
SverreK
8 years agoNew Member
Hi, I have a table also containing the price of all the different sales and want to calculate the total sale on the customers who buy all the selected products. Is there a way to change the DAX to make a sum of sales instead of a count of customers buying the selected products?
- alexanderkall3 years agoNew Member
Wondering if anyone has found a solution to this?