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!

     

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

awitt - OK, so how do you know the correct order id? Sorry, I'm missing something. Is it the Tracking number? If so, would suggest doing a Merge against itself based on the tracking number and then go from there.

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