Forum Discussion

Kipple123's avatar
Kipple123
Frequent Visitor
7 years ago
Solved

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 ...
Need Help
  • Kipple123's avatar
    Kipple123
    7 years ago

    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-xicai's avatar
v-xicai
Icon for Community Support rankCommunity Support
7 years ago

  

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's avatar
    Kipple123
    Frequent Visitor
    7 years ago

    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.