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

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:

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?

      • alexanderkall's avatar
        alexanderkall
        New Member
        3 years ago

        Wondering if anyone has found a solution to this?

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

      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
ADDCOLUMNS (
    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 :)