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
Vvelarde's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
10 years ago

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

 

EDIT: Sorry, Its correct.

 

 

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