topic Frequent bought products in DAX Commands and Tips

Frequent bought products

Anonymous — Wed, 23 Feb 2022 09:32:26 GMT

I need help with one of my projects on frequent bought products:

Customer who bought product X&Y also bought Z. If slicer selection is X and Y the table visual should reflect the frequently bought product as X, Y and Z with the order quantity and Total Price
Dax to find out Average order value of products selected in a slicer.

My requirement is to fetch products which are bought in same order frequently with the selected products in the slicer. Similar to the Recommendation Engine in Ecommerce sites which provides the insight on ‘Customers also purchased this Product combinations’.

Tables Orders

Table Filter Product

Data Modelling:

Slicer Selection - X, Y (Filter Product)

Expected -

Actual -

Formula I used for Quantity and Price is :

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] )))

Price for Customers who bought Filter Product =

CALCULATE ([Revenue],CALCULATETABLE (

SUMMARIZE ( Orders, Orders[Customer] ),

ALL ( Orders[Product] ),

USERELATIONSHIP ( Orders[Product], 'Filter Product'[Filter Product] )))

I tried several logics, but nothing seems to be working. Is there anyway someone could help me with the logic?

Re: Frequent bought products

goncalogeraldes — Mon, 21 Feb 2022 17:55:07 GMT

Anonymous Customer A bought X, Y and also W so I guees the result you get are correct because customer bought X, Y, W and customer B bought X, Y , Z. If you want to get you need, you have to introduce a condition to your formula where the Order No needs to be the same, like:

Your measure = CALCULATE ( SUM ( 'Orders'[Quantity] ), [Order No] = EARLIER ( [Order No] ) )

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

Re: Frequent bought products

Anonymous — Mon, 21 Feb 2022 18:00:22 GMT

Hi Anonymous - The relationship appears to be inactive, so it will not apply the filter. Consider adding the following measure:
CALCULATE ( [Count] , USERELATIONSHIP( 'Orders'[Product] , 'Filtered Products'[Filtered Product] ))

Re: Frequent bought products

Anonymous — Mon, 21 Feb 2022 18:17:16 GMT

Hi goncalogeraldes,

Customer A bought X, Y and also W but in separate orders. If I am selecting X and Y, it should only give XY and Z in the outcome as they are bought in same order.

Re: Frequent bought products

Anonymous — Mon, 21 Feb 2022 18:13:27 GMT

Hi Anonymous,

I tried your measure formula, but its not working. It is only fetching selected products. my requirement is to fetch products which are bought in same order frequently with the selected products in the slicer. In my case it should be X, Y and Z with there order quantity and Total Price. please see the output screenshot from the dax : CALCULATE ( [Count] , USERELATIONSHIP( 'Orders'[Product] , 'Filtered Products'[Filtered Product] ))

Re: Frequent bought products

Anonymous — Wed, 23 Feb 2022 09:35:35 GMT

For future reference, the solution that worked in my case is provided below. Updated my Order Quantity and Price logics, please find teh updated logic below:

Order Quantity for Customers who bought Filter Product =

VAR _selected=DISTINCT('Filter Product'[Filter Product])

VAR __Orders = SELECTCOLUMNS(FILTER(DISTINCT(Orders[Order No]),

CALCULATE(DISTINCTCOUNT(Orders[Product]),TREATAS( _selected, Orders[Product])) = COUNTROWS(_selected)

),"__Order",[Order No])

RETURN CALCULATE ([Order Quantity],FILTER(Orders,[Order No] IN __Orders) )

Price for Customers who bought Filter Product =

VAR _selected=DISTINCT('Filter Product'[Filter Product])

VAR __Orders = SELECTCOLUMNS(FILTER(DISTINCT(Orders[Order No]),

CALCULATE(DISTINCTCOUNT(Orders[Product]),TREATAS( _selected, Orders[Product])) = COUNTROWS(_selected)),"__Order",[Order No])

RETURN

CALCULATE ([Revenue],FILTER(Orders,[Order No] IN __Orders))

Output now getting as expected:

Re: Frequent bought products

kartikmeher — Fri, 11 Nov 2022 06:54:08 GMT

Hi , is there anyway to optimise this query?