Find the Next Product Purchased that is Different from the First

[ejhornyak]

I am trying to do some analysis around the order in which customers purchase products.  I have a table that has customers, date purchased, and product type.  I was able to find the first product purchased using the measure:

1st Product=

MAXX(

Topn(1,

Summarize(Contracts,_Date[Date],Contracts[Product Type]),

Calculate(MIN(_Date[Date])),ASC),Contracts[Product Type])

My Data looks like this:

Customer	Date	Product
Cust 1	11.19.2014	Product 1
Cust 1	4.4.2015	Product 1
Cust 1	8.13.2015	Product 4
Cust 1	8.15.2015	Product 4
Cust 1	9.2.2015	Product 3

I am trying to write a measure to return product 4 as the second unique product they purchased, and then product 3 as the 3rd in a separate measure.

The goal would be to combine the measures to look like this:

Customer	Product Sequence
Cust 1	Product 1, Product 4, Product 3

Thank you for the help!

[Ashish_Mathur · Accepted solution]

Hi,

Try these measures

Date value = MIN(Data[Date])

Product sequence = CONCATENATEX(TOPN(10,VALUES(Data[Product]),[Date value]),Data[Product], ", ")

Hope this helps.

[amitchandak]

@ejhornyak , Try like

concatenatex(summarize(Table,Table[customer],Table[Product],"_1",firstnonblankvalue(table[product],min(Table[Date]))),[product])