Forum Discussion
DAX Function for 'Customers also bought'
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
Super UserSure 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
Helper IIIWorks like a charm!