Multiple tables with the same columns. How to tell Power BI these columns are the same.

[Power_BI_Outlaw]

Good Evening,

Our company utilizes Peloton SiteView to manage cost data for various oil and gas leases. I've been able to butcher together a Power BI dataset that is capable of providing visuals to management and allow our team to quickly "data dump" cost data into excel workbooks that we utilize for cost reporting.

Most of my skill has been trial and error and how I've gotten this far, nobody knows lol. I've been utilizing Power BI for about 3 years and manage to make things work through a bunch of "band-aid patches". I have no programming skills and my DAX is beginning at best.

The tables in my dataset utilize a parent / child relationship between tables. That is how the cost program tables are setup. My gut is these tables weren't setup or optimized for data analytic programs.

I've included a snippet below that simplifies the relationship of the dataset. Each row in every table has it's own unique record ID. In additon, every row has the parents table record ID. Where my problem lies is that the Estimate, Forecast and Cost table all have the same column for cost codes.

I can complete the following table visual with Estimate, Forecast and Cost amounts rolled up to specific projects and jobs. 

However, when I try to introduce the Cost Codes column, everything blows up. For example if I take the Cost Codes Column for the Estimates table, the Estimate Amount will correctly be broken out for the Cost Codes. However, the Forecast Amounts and Cost Amounts will have the total Job costs for each Cost Code. I've tried everything I can think of to make this work. I would like to think Power BI could recognize the same column between tables and present the data correctly.

Any insights on getting this to work?

[Power_BI_Outlaw]

Hey @m_alireza 

Thanks for your comments. 

I think I understand what your are proposing. The challenge is my tables follow a "one to many" relationship. For context, I have ~10,000 project records, and ~100,000 job records. Each job record will have ~30 Estimate records comprised of cost codes, ~30 Forecast records comprised of cost codes, and ~500 Date records. Each date record contains ~10 cost records comprised of cost codes. 

To create a DIM table with JOB ID's and Cost codes would be millions of records. It would also create a "many to many" relationship between DIM table and my child tables if I understand correct. I've included a snippet below of what I believe you intended:

I did test it without getting it to work. I appended and merged my child tables to create a unique list of JOB ID's and Cost codes. 

[CoreyP]

I think you just need a Cost Code DIM with only a single column for Cost Code, no need to include Job ID. Just make sure it's a distinct list of cost codes than can appear in any table of your model containing cost codes. Then hook this up 1:* in your star schema. 

[m_alireza]

Hi @Power_BI_Outlaw ,

Do you have a DIM table for Cost Code? Like how you have one for Job Table and Project Table?

Just from looking at your diagram, it appears that thats whats missing. 

You need a table that has a list of unique cost codes and also associated Job IDs  and use that to create a relationship to your other tables. You should use the Cost Codecolumn from the DIM Table with the unique cost codes in your table visual as opposed to one from your other three tables.

I think the reason its not working is because Power BI doesnt have a common cost code link between all of your three tables. For example, with Job Name, you are taking that from the Job Table so it works because your Forecast Table, Estimate Table and Cost Table all connect to that. Same with Project Name, since it links with the Job Table.