Forum Discussion
How to make a calculated column that pulls in values from multiple tables based on two criterias?
I have one table with production times, and 4 others with the nominal times. I am trying to make a calculated column that will bring in the nominal time based on the recipe and machine. I have it working with one table, but the other 3 are having relationship issues which won't allow the column to work correctly. Below is an example of my data along with my current code. Any help would be appreciated!
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])))))
2 Replies
- lbendlin
Super User
Please 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/1447523Table 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])))))
