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

  • Kipple123's avatar
    Kipple123
    Frequent 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-xicai's avatar
    v-xicai
    Community 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.

    • Kipple123's avatar
      Kipple123
      Frequent Visitor

      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.