Forum Discussion

Kfausch's avatar
Kfausch
Helper II
6 years ago
Solved

Look up value formula

Hello,

 

Below is an example if the data I am working with. I am in a manufacturing setting, where 1 document has two entry types; output and consumption. The column called "Output Lot No." is what I am trying to do in Power BI. Essentially I am looking for a formula that will look up the lot number used for output and apply it to all lines with the same Document No. but I cannot find anything that works.

 

 

Any help would be greatly appreciated!

Kevin Fausch

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Try

    new Column =
LOOKUPVALUE('Table'[Lot No.], 'Table'[Entry Type], "Output", 'Table'[Document No.], firstnonbank('Table'[Document No.],true()))
OR
new Column =
minx(filter('Table', 'Table'[Entry Type]= "Output" && 'Table'[Document No.]= earlier('Table'[Document No.])),'Table'[Lot No.])

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Try

    new Column =
LOOKUPVALUE('Table'[Lot No.], 'Table'[Entry Type], "Output", 'Table'[Document No.], firstnonbank('Table'[Document No.],true()))
OR
new Column =
minx(filter('Table', 'Table'[Entry Type]= "Output" && 'Table'[Document No.]= earlier('Table'[Document No.])),'Table'[Lot No.])
    • Kfausch's avatar
      Kfausch
      Helper II
      6 years ago

      Great the second formula worked! Thank you!!

       

      test lot = minx(filter('ILE - Production', 'ILE - Production'[Entry_Type]= "Output" && 'ILE - Production'[Document_No]= earlier('ILE - Production'[Document_No])),'ILE - Production'[Lot_No])

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Kfausch 

    try a column

    Column =
LOOKUPVALUE('Table'[Lot No.], 'Table'[Entry Type], "Output", 'Table'[Document No.], [Document No.])

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    • Kfausch's avatar
      Kfausch
      Helper II
      6 years ago

      I am getting this error "A table of multiple values was supplied where a single value was expected." I think its because the table has multiple production Document No.'s.

       

      Thanks for you input!