Forum Discussion

jmukhtar's avatar
jmukhtar
Regular Visitor
4 years ago
Solved

First vs Second purchase Products

Hi   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 wo...
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity Champion
4 years ago

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!

  • jmukhtar's avatar
    jmukhtar
    Regular Visitor
    4 years ago

    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