Another Relationship Issue

[ntravis]

I am trying to compare estimates to actuals for projects.  Here are the relationships:

Job Master has Unique Job#s and are linked to the estimate table as well as the cost detail table.

Although, when I try and compare estimates to actual costs, I keep getting a repetition error:

Depending on which table I pull the 'CostItem' from changes which column has the repetition.

I can't wrap my head around how to correct this as I am not using a calculated column, or a measure for the data fields, just 'Amount' from the JobCostDetail table, and 'EstimatedDollars' from the JobEstimatesOriginal table.

Any thoughts here?

Thanks in advance,

Nathaniel Travis

[joerykeizer]

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!

[ntravis · Accepted solution]

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!

[ankitpatira]

@ntravis I have run into similar issues in past. since there is no active relationship between estimate and cost table you won't be able to visualise them in a single visual together. If you click on field dropdown under Axis and choose Don't summarize, visual will throw error. What you need to do is one of below,

1. either use cost and estimate from one table ie estimate table

2. create two visuals side by side one with job number and estimate and other with job number and cost