Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ErikOmni
Helper I
Helper I

Return the biggest product category (by sales) for the first order for each unique customer

Hi!

 

I'm trying to analyse the behavior of customers based on what the bought in their first order. I want to tag each customer based on the biggest product category (by sales) of their first purchase. This would allow me to easily analyse their behavior. Honestly, I'm quite lost even how to start attacking this problem and haven't been able to track down a solution in the forum.

 

Data consists of order data with rows for each item in the order. Below you see in a example of how the data looks like and the yellow column is the end result I'm looking for.

 

ErikOmni_0-1663434718825.png

 

In above example one customer has made two orders (first order containing three items, second order containing two items). The first order this customer ever placed was 2022-08-31. The biggest category in terms of revenue in the first order was Bedset (710 is bigger than 510). So I then want this customer to be tagged as "Bedsets".

 

Note: Data stretches from 2020-01-01 to 2022-08-31 and plenty of customers have first order date before 2020-01-01 so those customer could just be left as blank in the column.

 

Thanks in advance! 

 

 

 

 

1 ACCEPTED SOLUTION

@ErikOmni I used the wrong column for filtering:

Column = 
    VAR __Cust = [CustomerID]
    VAR __First = [First order date]
    VAR __Table = FILTER('Table7',[CustomerID] = __Cust && [Date] = __First)
    VAR __Max = MAXX(__Table,[Revenue])
    VAR __Category = MAXX(FILTER(__Table,[Revenue] = __Max),[Category])
RETURN
    __Category

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

@ErikOmni Try:

Column = 
    VAR __Cust = [CustomerID]
    VAR __First = [First order date]
    VAR __Table = FILTER('Table7',[CustomerID] = __Cust && [First order date] = __First)
    VAR __Max = MAXX(__Table,[Revenue])
    VAR __Category = MAXX(FILTER(__Table,[Revenue] = __Max),[Category])
RETURN
    __Category

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi! First of all, thank you so much for answering @Greg_Deckler . You have helped me out multiple times!

 

The formula you suggested didn't fully do the trick. This is an example of the outcome I got:

 

ErikOmni_2-1663524095783.png

 

 

 

In this case I have 1 customer that has made 5 orders. First order was made on 7 January 2021 and the biggest category in that order was Bedsets. However, the formula seems to take the biggest category for all orders that this customer has made.

 

When I implemented your formula it looked like this. It's a bit weird that it didn't work since it is filtered on first order date. 

 

ErikOmni_3-1663524321639.png

 

Any ideas?

@ErikOmni I used the wrong column for filtering:

Column = 
    VAR __Cust = [CustomerID]
    VAR __First = [First order date]
    VAR __Table = FILTER('Table7',[CustomerID] = __Cust && [Date] = __First)
    VAR __Max = MAXX(__Table,[Revenue])
    VAR __Category = MAXX(FILTER(__Table,[Revenue] = __Max),[Category])
RETURN
    __Category

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

Thank you! this is the third time you have helped me out. I've learned some new things from every post. 

@ErikOmni Happy to help! 🙂


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors