Forum Discussion

WillT's avatar
WillT
Icon for Community Admin rankCommunity Admin
10 years ago

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 Pat...
Show and Tell
Sean's avatar
Sean
Icon for Community Champion rankCommunity Champion
10 years ago

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

 

Sean's avatar
Sean
Icon for Community Champion rankCommunity Champion
10 years ago

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 :smileyhappy:

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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

     

    Thank you!

  • WillT's avatar
    WillT
    Icon for Community Admin rankCommunity Admin
    10 years ago

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

    • OwenAuger's avatar
      OwenAuger
      Icon for Super User rankSuper User
      10 years ago

      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

      • SverreK's avatar
        SverreK
        New Member
        8 years ago

        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?