cancel
Showing results for
Did you mean:
Frequent Visitor

## 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!

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

## Re: Adding Customer Order Number Column

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)

3 REPLIES 3
Community Support Team

## Re: Adding Customer Order Number Column

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.

Frequent Visitor

## Re: Adding Customer Order Number Column

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.

Frequent Visitor

## Re: Adding Customer Order Number Column

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)

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 157 members 1,889 guests
Recent signins: