cancel
Showing results for
Did you mean:
Moderator

Customers 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 Patterns, Chris Webb etc.) but they were always about a max of two products and used a pattern with multiple 'dimension' tables and meant you had to deal with inactive relationships.

We found a nice way of simplifying this so with one slicer you can select a few products and see how many people bought all of those products, and how many bought any of them:

You can get the pbix file here:

https://1drv.ms/u/s!AhrHQG3fCq7Rsu0Z8MhPKuWN3nkt6A

Hope you find it useful!

8 REPLIES 8
Super Contributor

Re: Customers who bought multiple products

Hi, In the PowerBI Sample you can't select more than 1 product.

EDIT: Sorry, Its correct.

Lima - Peru

Proud to be a Datanaut!

Super Contributor

Re: Customers who bought multiple products

@Vvelarde Not sure what you mean - you can CTRL select or just turn off single select (in the pbix file)

EDIT: CTRL Select works on the published report

Super Contributor

Re: Customers who bought multiple products

Here's the Measure for everyone to see!

```Buyers of All Selected Products =
IF (
ISBLANK (
COUNTROWS (
FILTER (
SUMMARIZE (
Sales,
Sales[Customer],
"ProductsBought", DISTINCTCOUNT ( Sales[Product] )
),
[ProductsBought] = COUNTROWS ( VALUES ( Sales[Product] ) )
)
)
),
0,
COUNTROWS (
FILTER (
SUMMARIZE (
Sales,
Sales[Customer],
"ProductsBought", DISTINCTCOUNT ( Sales[Product] )
),
[ProductsBought] = COUNTROWS ( VALUES ( Sales[Product] ) )
)
)
)```

Thanks to @WillT

Moderator

Re: Customers who bought multiple products

Wow, you jumped on this quickly! I've just updated the report so the slicer multi-selects by default 🙂 And thank you Sean for pulling the formula out!

I'd love to see if any of the DAX gurus around here can find a more efficient way to do this...

Super Contributor

Re: Customers who bought multiple products

HI 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.

1. 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```
2. 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```
3. 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

Proud to be a Datanaut!

Super Contributor

Re: Customers who bought multiple products

In case it's of interest, I used this dummy DAX Query to test performance of the different measures in DAX Studio.

It evaluates the chosen measure for every permutation of 5 products (with repetitions).

Measures v3 & v4 are noticeably faster.

```EVALUATE
CROSSJOIN (
SELECTCOLUMNS ( VALUES ( Sales[Product] ), "Product 1", Sales[Product] ),
SELECTCOLUMNS ( VALUES ( Sales[Product] ), "Product 2", Sales[Product] ),
SELECTCOLUMNS ( VALUES ( Sales[Product] ), "Product 3", Sales[Product] ),
SELECTCOLUMNS ( VALUES ( Sales[Product] ), "Product 4", Sales[Product] ),
SELECTCOLUMNS ( VALUES ( Sales[Product] ), "Product 5", Sales[Product] )
),
"Measure value", CALCULATE (
/* Replace with test measure */
[Buyers of All Selected Products v4],
Sales[Product] = EARLIER ( [Product 1] )
|| Sales[Product] = EARLIER ( [Product 2] )
|| Sales[Product] = EARLIER ( [Product 3] )
|| Sales[Product] = EARLIER ( [Product 4] )
|| Sales[Product] = EARLIER ( [Product 5] )
)
)```

Owen 🙂

Proud to be a Datanaut!

Occasional Visitor

Re: Customers who bought multiple products

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?

Regular Visitor

Re: Customers who bought multiple products

Hi,

This Solution is working for me and going fantastic but can i show the name of customers who bought all products also?

I am unable to show the customers name with this DAX.

Thank you!

Announcements

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!