Look up value formula

[Kfausch]

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

[amitchandak · Accepted solution]

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]

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]

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]

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!