cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kipple123 Frequent Visitor
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.

 

Order Number Example.JPG

 

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
Kipple123 Frequent Visitor
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
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.

Kipple123 Frequent Visitor
Frequent Visitor

Re: Adding Customer Order Number Column

@v-xicai 

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.

Kipple123 Frequent Visitor
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)

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 157 members 1,889 guests
Please welcome our newest community members: