Forum Discussion
Need help with relationship between tables
Hi,
I am trying to calculate the difference in run time based on the calculated run time and the actual run time on a production order.
The data on the actual run time comes from a "CapacityTransaction" table, and the data for the calculated time comes from a "ItemRoutingRows" table. I am having trouble connecting these two tables in order to compare the two.
Below is sample data from the two tables, when I am trying to connect the tables by "Routing_No" i can just get a "Many to Many" relationship which then sums up the total calculated run time for all the rows on an item and displays the total time instead of each individual time on each row.
ItemRoutingRows Table below,
| Routing_No | Operation_No | Type | No | Description | Routing_Link_Code | Run_Time |
| 10098-05 | 10 | Work Center | FUSE | Formlabs Fuse 1 SLS Printer | SLS | 0.177 |
| 10098-05 | 20 | Work Center | GB | Glasblästring | BLÄSTER | 0.007 |
| 10098-05 | 30 | Work Center | IFG | Infärgning | INFÄRG | 0.001 |
| 10098-05 | 40 | Work Center | VMI | Kvalitetskontroll | KVAL | 0.002 |
| 10098-05 | 50 | Work Center | BEARB | Bearbetning SLS | BEARB | 0.02 |
CapacityTable below,
| Document_No | Routing_No | Operation_No | Type | No | Description | Run_Time |
| PS006169 | 10098-05 | 10 | Work Center | FUSE | Formlabs Fuse 1 SLS Printer | 40 |
| PS006169 | 10098-05 | 20 | Work Center | GB | GLASBLÄSTRING | 2 |
| PS006169 | 10098-05 | 30 | Work Center | IFG | Infärgning | 0.6 |
| PS005839 | 10098-05 | 10 | Work Center | FUSE | Formlabs Fuse 1 SLS Printer | 23 |
| PS005839 | 10098-05 | 20 | Work Center | GB | GLASBLÄSTRING | 1 |
| PS005839 | 10098-05 | 30 | Work Center | IFG | Infärgning | 0.3 |
| PS005839 | 10098-05 | 40 | Work Center | QC | Kvalitetskontroll | 0.2 |
| PS005839 | 10098-05 | 50 | Work Center | BEARB | Bearbetning SLS | 0.4 |
| PS005256 | 10098-05 | 10 | Work Center | FUSE | Formlabs Fuse 1 SLS Printer | 23 |
| PS005256 | 10098-05 | 20 | Work Center | GB | GLASBLÄSTRING | 1 |
| PS005256 | 10098-05 | 30 | Work Center | IFG | Infärgning | 0.3 |
| PS005256 | 10098-05 | 40 | Work Center | QC | Kvalitetskontroll | 0.2 |
| PS005256 | 10098-05 | 50 | Work Center | BEARB | Bearbetning SLS | 1 |
Hi, Bondeuz
Based on your description, I have created many measures to achieve the effect you are looking for. Following picture shows the effect of the display.
Measure:
Operation_No1 = CALCULATE ( MAX ( CapacityTransaction[Operation_No] ) ) No1 = CALCULATE ( MAX ( CapacityTransaction[No] ) ) Run_Time1 = CALCULATE ( MAX ( CapacityTransaction[Run_Time] ) ) Time_Diff = VAR CurrentOperation = CapacityTransaction[Operation_No1] VAR CurrentNo = CapacityTransaction[No1] VAR CurrentRunTime = CapacityTransaction[Run_Time1] VAR RelatedRow = CALCULATETABLE ( ItemRoutingRows, ItemRoutingRows[Operation_No] = CurrentOperation, ItemRoutingRows[No] = CurrentNo ) VAR RelatedRunTime = MAXX ( RelatedRow, ItemRoutingRows[Run_Time] ) RETURN CurrentRunTime - RelatedRunTime
Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
2 Replies
- amitchandak
Super User
Bondeuz , You can create a new column in both tables and join. Seem like this combined columns are key for table 1
Key - combinevalues("-",[Routing_No], [Operation_No])
Hi, Bondeuz
Based on your description, I have created many measures to achieve the effect you are looking for. Following picture shows the effect of the display.
Measure:
Operation_No1 = CALCULATE ( MAX ( CapacityTransaction[Operation_No] ) ) No1 = CALCULATE ( MAX ( CapacityTransaction[No] ) ) Run_Time1 = CALCULATE ( MAX ( CapacityTransaction[Run_Time] ) ) Time_Diff = VAR CurrentOperation = CapacityTransaction[Operation_No1] VAR CurrentNo = CapacityTransaction[No1] VAR CurrentRunTime = CapacityTransaction[Run_Time1] VAR RelatedRow = CALCULATETABLE ( ItemRoutingRows, ItemRoutingRows[Operation_No] = CurrentOperation, ItemRoutingRows[No] = CurrentNo ) VAR RelatedRunTime = MAXX ( RelatedRow, ItemRoutingRows[Run_Time] ) RETURN CurrentRunTime - RelatedRunTime
Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
