Forum Discussion
Another Relationship Issue
I actually used this approch somewhat, although I had to use a concatenate function below to get a unique key in the estimate table and the job cost table:
JobCostItem = Concatenate(concatenate(JC_JobEstimatesOriginal[JobNumber],JC_JobEstimatesOriginal[CostItem]),JC_JobEstimatesOriginal[CostType])
Not sure if there was a better formulae to combine the three columns, but you're right. Once I had these two tables linked properly everything worked great!
The problem occurs because your relationship is based on the Job only. It doesn't know how the items in the Cost and Estimate table relate to each other.
You can fix it like this: Link JobMaster to JobCost on Job and then JobCost to JobEstimate based on a custom made identifer (I assume it's the Job+Item combination). You cannot link on multiple columns so you have to create a new one and do it like this.
Good luck!
I actually used this approch somewhat, although I had to use a concatenate function below to get a unique key in the estimate table and the job cost table:
JobCostItem = Concatenate(concatenate(JC_JobEstimatesOriginal[JobNumber],JC_JobEstimatesOriginal[CostItem]),JC_JobEstimatesOriginal[CostType])
Not sure if there was a better formulae to combine the three columns, but you're right. Once I had these two tables linked properly everything worked great!