Forum Discussion
Adding Customer Order Number Column
I'm working with a table that has unique Customer ID's and Order IDs, but has one row per item in a given order. I'm trying to add a calculated column to the table that will tell me whether the row belongs to their first order, second order, etc...
Here's a simplified example table with the desired Order # column that I'm trying to add.
I can't figure out how to add a count based on both a unique customer ID and for that customers unique OrderID.
I've been trying variations on this code, but whenever I add in customerID it either crashed Power BI or returns something else that's not what I need.
Order# =
VAR CurrentDate = Sales[order_date]
RETURN COUNTROWS (
FILTER(
CALCULATETABLE (
Sales,
ALLEXCEPT(Sales, Sales[customerId])
),
Sales[order_date] < CurrentDate
|| ( Sales[order_date] = CurrentDate )
)
)
Any help would be much appreciated! Thanks!
I was able to solve this problem after I learned about the "earlier" function. Here's the code that worked
Rank = RANKX(FILTER(Sales,Sales[customerId]=EARLIER(Sales[customerId])),Sales[order_date],,ASC,Dense)
4 Replies
- Kipple123Frequent Visitor
I was able to solve this problem after I learned about the "earlier" function. Here's the code that worked
Rank = RANKX(FILTER(Sales,Sales[customerId]=EARLIER(Sales[customerId])),Sales[order_date],,ASC,Dense)
- v-xicaiCommunity Support
Hi Kipple123 ,
You can create Order # column like DAX below.
Order #= MID(Sales[OrderId],2,1)
If you need to count based on both a unique customerId and for that customers unique OrderId, create measure like DAX below.
Count = CALCULATE(COUNT(Sales[order_date]),FILTER(ALLSELECTED(Sales),Sales[customerId]=MAX(Sales[customerId])&&Sales[OrderId]=MAX(Sales[OrderId])))
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Kipple123Frequent Visitor
Thanks for your help!
Unfortunately the Order# calculation your provided won't work with the real data. The real data the orderID is 8-16 character long string containing numbers and characters. I just built the example table this way so it was easy to follow, sorry it was misleading.
The second DAX measure for the count is only returning blanks. I believe it's because the orderID is a string not a number. I'll try some variations on it though.
- SunnieHelper I
this video is helpful. https://www.youtube.com/watch?v=4xapOrfLvbg