Forum Discussion

JosephJC's avatar
JosephJC
Regular Visitor
9 years ago
Solved

DAX Function for 'Customers also bought'

My Power BI dashboard contains a product filter and I would need to create a Power BI chart to show 'Customers who bought this product also bought the following ...'. Which would be a bar chart of t...
Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    Hi JosephJC

     

    A Basket Analysis pattern will work for this.

    http://www.daxpatterns.com/basket-analysis/

     

    Here's a dummy example pbix

     

    First create a 'Filter Product' table with an inactive relationship with your Orders table:

    You use the 'Filter Product' table to select the product(s) to be analysed.

     

    Then, assuming you have created your base measures for Quantity/Price etc, you create 'basket analysis' measures with this pattern (taken from DAX Patterns):

    Order Quantity for Customers who bought Filter Product = 
CALCULATE (
    [Order Quantity],
    CALCULATETABLE (
        SUMMARIZE ( Orders, Orders[Customer] ),
        ALL ( Orders[Product] ),
        USERELATIONSHIP ( Orders[Product], 'Filter Product'[Filter Product] )
    )
)

    This measure will return the Order Quantity for any Products purchased by Customers who purchased the products selected in 'Filter Product'.

     

    Hopefully this or something similar meets your needs.

     

    Cheers,

    Owen

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
4 years ago

Sure thing 🙂

I have updated my sample PBIX with an example - attached.

In this model, I have also created 'Product' and Customer dimensions to better represent a typical model, and adjusted the measures accordingly.

 

Here is one of the new measures, that returns Quantity for Customers who bought the selected "Filter Products", but only for products other than the selection:

 

Order Quantity for Customers who bought Filter Product (excl Filter Product) = 
CALCULATE (
    [Order Quantity],
    CALCULATETABLE (
        SUMMARIZE ( Orders, Customer[Customer] ),
        ALL ( 'Product' ),
        USERELATIONSHIP ( Orders[Product], 'Filter Product'[Filter Product] )
    ),
    KEEPFILTERS (
        EXCEPT (
            VALUES ( 'Product'[Product] ),
            VALUES ( 'Filter Product'[Filter Product] )
        )
    )
)

Regards,

Owen

 

WettenVamos's avatar
WettenVamos
Frequent Visitor
3 years ago

Hi Owen,

 

I have a similar problem with subscription. I think your solution will work fine, but i need an extra check. Subscription have a start and an end date, and i want to check if the subscription were active during the same period. 

 

Can you help me?

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

    Hi WettenVamos 

    Yes, I think I can help with that.

    Just to confirm the requirements:

    Comparing with the example above, rather than "Products" you have "Subscriptions".

    Each Subscription has a start & end date.

    Any given customer may have purchased multiple Subscriptions

    Then (as an example) you want to be able to see for customers that purchased Subscription A, which other Subscriptions did they purchase, as long as the Subscriptions overlap in time?

     

    Could you show what your tables or data model look like?

     

    Regards,

    Owen