topic Re: First vs Second purchase Products in DAX Commands and Tips

First vs Second purchase Products

jmukhtar — Mon, 27 Dec 2021 07:32:02 GMT

I am working on a requriement to generate visualize what products customer bought in second orders based on products that are purchased in first order and i am really struggling to get this working.

If a 100 customers bought product A in first order and out of those 100 customers 50 bought Product B and 30 bought Product C and 20 bought Product D in second orders. This neeeds to be presented like this

Data that i currently have looks like this where sortorder is the first or second order number for the customer

We have full control of data and have all of sales and product data available to us and can format data as we require.

I have tried quite a few things but not able to get this working properly.

Any help on how to do it would be really really helpful

Thanks,

Re: First vs Second purchase Products

ValtteriN — Mon, 27 Dec 2021 08:08:25 GMT

Hi,

Here is one way to do this:

Start data:

Dax (measure):

FirstOrder =

var Fdate = calculate(FIRSTDATE(FirstOrder[Date]),all(FirstOrder),FirstOrder[Customer]=max(FirstOrder[Customer]))

var Sdate = CALCULATE(FIRSTDATE(FirstOrder[Date]),all(FirstOrder),FirstOrder[Date]>Fdate,FirstOrder[Customer]=max(FirstOrder[Customer]))

var Cdate = MAX(FirstOrder[Date])

return

switch(true(),

Cdate=Fdate,"First Order",

Cdate=Sdate,"Second Order",

"Other")

End result:
Note that you need to use filter similar to the one in the picture to get the desired output.

I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!

Re: First vs Second purchase Products

jmukhtar — Mon, 27 Dec 2021 09:29:18 GMT

Hi ValtteriN

This doesn't work for us as we are looking for a list of customers who have bought a specific product in first order and then use that list to see what these customers have bought in second order

Re: First vs Second purchase Products

ValtteriN — Mon, 27 Dec 2021 10:33:58 GMT

Hi,

In that case you can add this table:

Then you can use that in a table to get the list of customers who bought e.g. Product C in first order:

(add this to table visual ^^)

OrderTypeFilter = IF(

OR(HASONEVALUE('Order Type'[Order Type])=FALSE(),

MAX('Order Type'[Order Type])=FirstOrder[FirstOrder]),1,0)

Now edit interactions to disable product filter from second visual:

Filter second visual with "second"

And as a result we notice that customers A and B bought product C in the first order. Out of these customers A bought product D in second order.

Data matches this:

Re: First vs Second purchase Products

askhanduja — Mon, 27 Dec 2021 10:28:40 GMT

It would be helpful if you can attach a Power BI file with some sample data and screenshot of the desired output based on that sample data

Re: First vs Second purchase Products

jmukhtar — Mon, 27 Dec 2021 17:01:20 GMT

Hi ValtteriN

This solution gives us the first vs the second purchase, but What we want to get in second order visual is only the customers who have purchased from the selected category in first order. Please see the snapshot

1,3,6,8,9,10 customers bought product A in first order; and these customers bought product B, C and D in second order.

In second visual we want to see products bought by customers who have bought product A in first order

Re: First vs Second purchase Products

jmukhtar — Mon, 27 Dec 2021 17:02:38 GMT

Sample data is shared along with the requirements. I couldn't get it working on Power BI so far 😞

Re: First vs Second purchase Products

PaulOlding — Mon, 27 Dec 2021 21:29:22 GMT

Hi jmukhtar

Here's an option for this. It requires you to import a second copy of your products table that is disconnected from the rest of your model

Then you can have a Second Order Amount measure like this:

Second Order Amount = VAR _Customers = CALCULATETABLE( VALUES('Table'[customer_id]), 'Table'[sort_order] = 1 ) VAR _Result = CALCULATE( SUM('Table'[net_sales]), 'Table'[sort_order] = 2, _Customers, TREATAS(VALUES('Products 2'[product_name]),'Products'[product_name]) ) RETURN _Result

Finally, you'd use the product name from Products 2 table in your visual, along with the above measure.

Re: First vs Second purchase Products

Anonymous — Thu, 30 Dec 2021 02:56:49 GMT

Hi jmukhtar ,

I have created a data sample,

According to this requirement: we are looking for a list of customers who have bought a specific product in first order and then use that list to see what these customers have bought in second order

Please follow these steps:

1. Create a productId table for slicer

ForSlicer = VALUES('Table'[ProducrId])

2. Rank by Date

Rank = RANKX (ALL('Table'), CALCULATE ( MAX ( ( 'Table'[Date]) ) ),,ASC,Dense)

3. First flag measure , and apply it to filter pane, set as "is 1"

First = IF(MAX('Table'[ProducrId]) in ALLSELECTED(ForSlicer[ProducrId]),[Rank],BLANK())

4. Second flag measure, and apply it to filter pane, set as "is 1" as well

Second = var _allCustomers=SUMMARIZE(FILTER(ALL('Table'),[First]=1),[CustomerID]) return IF(MAX('Table'[CustomerID]) in _allCustomers && [Rank]=2,1,0)

Outputs:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: First vs Second purchase Products

kk_shp_user — Thu, 07 Nov 2024 14:17:00 GMT

Thanks for this approach! I found this easier!

Also, I used this to count distinct customers instead of sales. The only change I did was in the second order measure, I had to include ALL(Table), so that the filters for the first product did not affect this measure.

Second Order Amount = VAR _Customers = CALCULATETABLE( VALUES('Table'[customer_id]), 'Table'[sort_order] = 1 ) VAR _Result = CALCULATE( SUM('Table'[net_sales]), ALL('Table'), 'Table'[sort_order] = 2, _Customers, TREATAS(VALUES('Products 2'[product_name]),'Products'[product_name]) ) RETURN _Result