Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

find first value

I have the following two tables:

Order and OrdLn

I would like to find OrdLn[Price] where OrdLn[OrdNo] = Order[IDBakssystem] and OrdLn[ProdNo] = "TS-L" and store it in an calculated column TS-L in the Order table.

Unfortunately there are sometimes several instansces where this is true, and this results in an error.

I have looked at similar problems on the forum, but none that matches this or help me solving it.

I have tried with LOOKUP and CALCULATE and FILTERS using FIRSTNONBLANKS and EARLIER without luck.

OrdLn[OrdNo] is also a number and Order[IDBakssystem] is a text, so this needs to be converted with VALUE

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    7 years ago

    This has been solved by correcting the data in the database, eradicationg the original problem.

     

    Thank you very much for your assistance!

10 Replies

  • PattemManohar's avatar
    PattemManohar
    Community Champion
    7 years ago

    Anonymous  Please post the sample test data (that can be copied easily) and expected output to suggest an accurate solution.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      OK! :-)

       

      I think I solved it by creating two Columns:

      Value_IDBaksystem = IFERROR(VALUE( Ordre[IdBaksystem] );0)
      And then
      TS-L =
      CALCULATE (
      FIRSTNONBLANK( OrdLn[Price] ; 1 );
      FILTER (
      OrdLn;
      OrdLn[ProdNo] = "TS-L" && OrdLn[OrdNo] = Ordre[Value_IDBaksystem]
      )
      )
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      OrdLn

      OrdNoProdNoPrice
      73013TS-L8000
      73013TS-L0
      7301310300000390,456
      73013T40
      73013T40
      73013T40
      73013TS-L0
      73013 0

       

      However with the formula I thought worked, it still returns 0 not 8000.

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous ,

    Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered to close this topic?

     

    Regards,

    Daniel He

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi!

       

      Thank you for the follow up.

      No it is not solved yet as I have been given other task temporary at work, but I'll let you know as soon as I have tested it.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      This has been solved by correcting the data in the database, eradicationg the original problem.

       

      Thank you very much for your assistance!