Customers who bought multiple products

Question

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&nbsp;inactive relationships.
&nbsp;
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:
&nbsp;
https://app.powerbi.com/view?r=eyJrIjoiMmQ0OWYzYmQtNjhjNC00OGRiLWEzNGQtNGZjYWU4YWQzMTg1IiwidCI6ImFmZGIzOGQ2LTI0MTgtNDQyYS1hMDI1LWYwODY3MWYzMDRjYiIsImMiOjZ9&nbsp;
&nbsp;
&nbsp;You can get the pbix file here:
https://1drv.ms/u/s!AhrHQG3fCq7Rsu0Z8MhPKuWN3nkt6A
&nbsp;
Hope you find it useful!
&nbsp;

anonymous · Answer

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.Please help&nbsp;Thank you!

vvelarde · Answer

Hi, In the PowerBI Sample you can't select more than 1 product.&nbsp;EDIT: Sorry, Its correct.&nbsp;&nbsp;

sean · Answer

Here's the Measure for everyone to see!&nbsp;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] ) )
        )
    )
)&nbsp;Thanks to WillT&nbsp;:smileyhappy:

owenauger · Answer

HI there,Here&nbsp;are a few options I came up with :)&nbsp;The last one (v4) seems to run fastest but please test at your end with actual data.&nbsp;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] ) )
    )
)
    + 0Here is an alternative using EXCEPT on two lists of products to see if removing the&nbsp;selected products from&nbsp;each customer's list leaves an empty list (rather than&nbsp;comparing product counts):Buyers of All Selected Products v3 =
COUNTROWS (
    FILTER (
        VALUES ( Sales[Customer] ),
        ISEMPTY (
            EXCEPT (
                VALUES ( Sales[Product] ),
                CALCULATETABLE ( VALUES ( Sales[Product] ) )
            )
        )
    )
)
    + 0This 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.&nbsp;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]
        )
    )
)
    + 0All the best,Owen

sverrek · Answer

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?

Forum Discussion

Customers who bought multiple products

10 Replies

Related content

Unique Number of Customers who purchased multiple products

Segmentation based on Product List available for each Customer

multiple customer design in fabric

Configure RLS for multiple external customers?

Production Lessons