Forum Discussion
How to make a calculated column that pulls in values from multiple tables based on two criterias?
Super UserPlease provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
| Table 1 | Table Machine 1 (M1) | Table Machine 2 (M2) | |||||||||||||
| Operation | Step | Machine | Recipe | Prod | CycleTime | Recipe | Operation | Step | CycleTime | Recipe | Operation | Step | CycleTime | ||
| 1 | S1 | M1 | 123123 | 1.2 | 1.43 | 123123 | 1 | S1 | 1.43 | 123123 | 1 | S1 | 1.43 | ||
| 2 | S2 | M1 | 123123 | 1.34 | 1.43 | 123123 | 2 | S2 | 1.43 | 123123 | 2 | S2 | 1.43 | ||
| 3 | S3 | M2 | 123123 | 2 | 1.87 | 123123 | 3 | S3 | 1.87 | 123123 | 3 | S3 | 1.87 | ||
| 4 | S4 | M3 | 123123 | 1.87 | 2.1 | 123123 | 4 | S4 | 2.1 | 123123 | 4 | S4 | 2.1 | ||
| 5 | S5 | M4 | 123123 | 1.32 | 0.99 | 123123 | 5 | S5 | 0.99 | 123123 | 5 | S5 | 0.99 | ||
| 1 | S1 | M1 | 456456 | 0.65 | 1.23 | 456456 | 1 | S1 | 1.23 | 456456 | 1 | S1 | 1.23 | ||
| 2 | S2 | M2 | 456456 | 0.45 | 1.56 | 456456 | 2 | S2 | 1.56 | 456456 | 2 | S2 | 1.56 | ||
| 3 | S3 | M2 | 456456 | 0.33 | 1.15 | 456456 | 3 | S3 | 1.15 | 456456 | 3 | S3 | 1.15 | ||
| 4 | S4 | M4 | 456456 | 1.56 | 3.45 | 456456 | 4 | S4 | 3.45 | 456456 | 4 | S4 | 3.45 | ||
| 5 | S5 | M1 | 456456 | 1.78 | 6.87 | 456456 | 5 | S5 | 6.87 | 456456 | 5 | S5 | 6.87 | ||
| 1 | S1 | M3 | 89765 | 2.1 | 1.33 | 89765 | 1 | S1 | 1.33 | 89765 | 1 | S1 | 1.33 | ||
| 2 | S2 | M3 | 89765 | 4.1 | 1.45 | 89765 | 2 | S2 | 1.45 | 89765 | 2 | S2 | 1.45 | ||
| 3 | S3 | M4 | 89765 | 1.55 | 1.77 | 89765 | 3 | S3 | 1.77 | 89765 | 3 | S3 | 1.77 | ||
| 4 | S4 | M1 | 89765 | 1.67 | 0.87 | 89765 | 4 | S4 | 0.87 | 89765 | 4 | S4 | 0.87 | ||
| 5 | S5 | M2 | 89765 | 0.99 | 1.54 | 89765 | 5 | S5 | 1.54 | 89765 | 5 | S5 | 1.54 |
The bold column is whats expected using the code I shared originally. I need the cycle time from the machine tables inserted into table 1 based on the recipe and operation number. PowerBI keeps giving me relationship errors no matter which way I set the relationships up. I believe it has to do with the operation numbers being the same for different recipes, but I am not sure how to get around this. This is to show the actual time taken vs the nominal cycle time from the machine tables.
have attached code again;
CycleTime= IF(Hourly[Machine] = "M1" && Hourly[Operation] = RELATED(ProdTargetM1[Operation]), RELATED(ProdTargetM1[CycleTime]), IF(Hourly[Machine] = "M2" && Hourly[Operation] = RELATED(ProdTargetM2[Operation]) && Hourly[Recipe] = RELATED(prodtargetm2[RecipeName]), RELATED(ProdTargetM2[CycleTime]), IF(Hourly[Machine] = "M3" && Hourly[Operation] = RELATED(ProdTargetM3[Operation]) && Hourly[Recipe] = RELATED(prodtargetM3[RecipeName]), RELATED(ProdTargetM3[CycleTime]), IF(Hourly[Machine] = "M4" && Hourly[Operation] = RELATED(ProdTargetM4[Operation]) && Hourly[Recipe] = RELATED(prodtargetM4[RecipeName]), RELATED(PapHrProdTargetB02[CycleTime])))))