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 the products by Price.
My data is flattened, consisting of an Orders table with the following: Customer, Product, Price, Qty, Date.
Need help to build the DAX query for achieving this and handle the context correctly. I tried using ALL and FILTER but did not manage to get the correct result as yet.
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
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
Hi Owen, I know it's an old post but it was quite helpful.
Is there a way to filter out the product you choose in the filter? I want to see all OTHER products (assuming it should work on multiple selections too)
AlexaderMilland Glad to hear it 🙂
Just to clarify, since this is a Basket Analysis example, did you want the 'Filter Product' selection to be inverted?
For example, do you want to make the "primary" Product selection on one slicer, then make the 'Filter Product' selection on another slicer, and have a measure that returns Order Quantity for Customers that purchased the "primary" Product selection but not 'Filter Product' selection?
Regards,
Owen
Hi Owen,
Thank you so much for this! I have another question please, what formula should I include to sum up the quantity/product if i selected more than 1 product in the slicer. Thank you.
Hi Lesliekeziah
With the measure as it is, selecting multiple products on the slicer will return values for Customers that purchased any of the selected products.
Did you want a different behaviour? Could you provide a small example of the result you're wanting?
