Forum Discussion

awitt's avatar
awitt
Icon for Helper III rankHelper III
6 years ago
Solved

String Based New Column

I have data that has one record per concatenated OrderID & Tracking Number and some records have an incorrect OrderID that is throwing off the data. All correct OrderIDs start with a "1" so the line ...
Need Help
  • richbenmintz's avatar
    richbenmintz
    6 years ago

    Hi awitt,

     

    This calculated colulmn Formula should get the correct orderId for each tracking number

    Correct Order ID = 
    var trackingNumber = [Tracking Number]
return
    CALCULATE(max('Table'[Order ID]), FILTER(ALL('Table'), 'Table'[Tracking Number] = trackingNumber && LEFT('Table'[Order ID], 1) = "1"))

    Then this calc column would create the correct Order Track

    Correct OrderTrack = 'Table'[Correct Order ID] & 'Table'[Tracking Number]

    The Min Max Dates can then be derived with a measure or a calculated column.

     

    Hope this helps,

    Richard


    Did I answer your question? Mark my post as a solution!
    Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

     

     

awitt's avatar
awitt
Icon for Helper III rankHelper III
6 years ago

Greg_Deckler  The correct OrderID always starts with a 1 and the incorrect orderID never seems to start with a 1. The tracking numbers for both are always the same and correct. 

 

So something along the lines of Case/When '1%' if you were to do this in SQL. 

richbenmintz's avatar
richbenmintz
Icon for Resident Rockstar rankResident Rockstar
6 years ago

Hi awitt,

 

This calculated colulmn Formula should get the correct orderId for each tracking number

Correct Order ID = 
    var trackingNumber = [Tracking Number]
return
    CALCULATE(max('Table'[Order ID]), FILTER(ALL('Table'), 'Table'[Tracking Number] = trackingNumber && LEFT('Table'[Order ID], 1) = "1"))

Then this calc column would create the correct Order Track

Correct OrderTrack = 'Table'[Correct Order ID] & 'Table'[Tracking Number]

The Min Max Dates can then be derived with a measure or a calculated column.

 

Hope this helps,

Richard


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

 

 

  • richbenmintz's avatar
    richbenmintz
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    Hi awitt,

     

    Here are the measures to get the Min Max and days between

     

    Min Dep Date = CALCULATE(MIN('Table'[Departure Date]), FILTER(ALL('Table'), 'Table'[Correct Order ID] = MIN('Table'[Correct Order ID]))) 

Max Current Date = CALCULATE(Max('Table'[Current Date]), FILTER(ALL('Table'), 'Table'[Correct Order ID] = MIN('Table'[Correct Order ID]))) 

Days Between = DATEDIFF('Table'[Min Dep Date], 'Table'[Max Current Date], DAY)

     

    Hope this Helps,

    Richard


    Did I answer your question? Mark my post as a solution!
    Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!