Search one table to return value from corresponding column

[achurk]

Hello!

I have two data files "schedule_data" and "production_data"

schedule_data has both columns lot_number (example format 12345, 23456, 34567, etc) and batch_size (example format 1, 8800, 550, etc), while production_data only has lot_number.

The lot numbers in production_data are the same as the lot numbers in schedule_data but in a different order.

My goal is to create 2 table visualizations in my power bi dashboard. One with the columns from schedule_data: lot_number, batch_size and another with the columns from lot_number from production_data and batch size from schedule_data.

Is there a way to get the visualization to look up the batch_size from schedule_data and create a batch_size column for the lot numbers in production_data? Either in a table or in a visualization? I cant get them to match up correctly

Thank you!

[v-eqin-msft · Accepted solution]

Hi @achurk ,

I have built a simply data sample:

• Method1—— You could build a relationship based on lot_number between these two tables:

• Method2—— Use LOOKUPVALUE() if there is a unique batch_size of each lot_number:

Searched = LOOKUPVALUE(schedule_data[batch_size ],schedule_data[lot_number],'production_data'[lot_number])

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@achurk , You can but that might not be accurate, It can be Min/Max or Average of Size in another table for same lot number, if there is more than one size for the same lot number

a new column in production_data 

batch_size = Maxx(filter(schedule_data , production_data[lot_number]   =schedule_data [lot_number] ), schedule_data [batch_size] )