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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ErikOmni
Helper I
Helper I

Main Category (based on sales) for Second Order

Hi!

 

So I'm trying to create a column with the main product category in the second order from each individual customers.

 

I have already created a column for the First Order, since the date of the first order exists in the dataset. See below formula:

 

Main Category_First Order =
    VAR __Cust = [CustomerID]
    VAR __First = OrderData[Customer.FirstOrder.1]
    VAR __Table = FILTER(OrderData,[CustomerID] = __Cust && [OrderDate] = __First)
    VAR __Max = MAXX(__Table,[Total Sales])
    VAR __Category = MAXX(FILTER(__Table,[Total Sales] = __Max),[Category])
RETURN
    __Category
 
Then I created an Order Rank to allow me to identify each order
 
Order Rank =

rankx(filter(OrderData, [CustomerID] = earlier([CustomerID] ) ) , OrderData[OrderDate],,asc,dense)
 
However, now I tried to partially replicate the first methodology to create a column for the main category in the second order, like this:
 
Main Category_Second Order =
    VAR __Cust = [CustomerID]
    VAR __Second = OrderData[Order Rank] = 2
    VAR __Table = FILTER(OrderData,[CustomerID] = __Cust && OrderData[Order Rank] = __Second)
    VAR __Max = MAXX(__Table,[Total Sales])
    VAR __Category = MAXX(FILTER(__Table,[Total Sales] = __Max),[Category])
RETURN
    __Category
 
 
But that landed me this...
 
ErikOmni_0-1664991399213.png

 

Any ideas how one might solve this?

 

Thanks!

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@ErikOmni , there are two equal sign, that seem problem to me

 

Main Category_Second Order =
VAR __Cust = [CustomerID]
VAR __Second =  2
VAR __Table = FILTER(OrderData,[CustomerID] = __Cust && OrderData[Order Rank] = __Second)
VAR __Max = MAXX(__Table,[Total Sales])
VAR __Category = MAXX(FILTER(__Table,[Total Sales] = __Max),[Category])
RETURN
__Category

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@ErikOmni , there are two equal sign, that seem problem to me

 

Main Category_Second Order =
VAR __Cust = [CustomerID]
VAR __Second =  2
VAR __Table = FILTER(OrderData,[CustomerID] = __Cust && OrderData[Order Rank] = __Second)
VAR __Max = MAXX(__Table,[Total Sales])
VAR __Category = MAXX(FILTER(__Table,[Total Sales] = __Max),[Category])
RETURN
__Category

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.