Forum Discussion
Search one table to return value from corresponding column
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!
Hi Anonymous ,
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.
2 Replies
Anonymous , 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] )
Hi Anonymous ,
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.
