cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Find the Next Product Purchased that is Different from the First

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:
 
CustomerDateProduct

Cust 1

11.19.2014Product 1
Cust 14.4.2015Product 1
Cust 18.13.2015Product 4
Cust 18.15.2015Product 4
Cust 19.2.2015Product 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:

 

CustomerProduct Sequence
Cust 1Product 1, Product 4, Product 3

 

Thank you for the help!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Find the Next Product Purchased that is Different from the First

Hi,

Try these measures

Date value = MIN(Data[Date])
Product sequence = CONCATENATEX(TOPN(10,VALUES(Data[Product]),[Date value]),Data[Product], ", ")
Hope this helps.
Untitled.png

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

2 REPLIES 2
Highlighted
Super User IV
Super User IV

Re: Find the Next Product Purchased that is Different from the First

@ejhornyak , Try like

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


Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User III
Super User III

Re: Find the Next Product Purchased that is Different from the First

Hi,

Try these measures

Date value = MIN(Data[Date])
Product sequence = CONCATENATEX(TOPN(10,VALUES(Data[Product]),[Date value]),Data[Product], ", ")
Hope this helps.
Untitled.png

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors