Help to get budget details

[LuisVillarreal]

Good morning experts, I have a problem that I hope you can help me with.

Here is a file with a minimal reproducible example: link 

I have 4 tables:

1. The budget list for location and jobs/works
2. The details of the budget divided by location, work and material
3. Type 1 Extras (specified by location and job) with amount per material id
4. Type 2 Extras (specified only by location) with amount per material id

The relationships looks like this: map 

This is my first matrix only with the initial budgets: first matrix 

And here is the problem, when I want to display the two types of extras specified by material id: problem 

Basically, if I want to see the details of the budget for each material everything is fine, the problem starts when I want to see the detail of the extras divided by each location and material id.
Since the extras may be of a material that was not initially budgeted, this is where my struggle begins.

I have not been able to show the extras in the right material, I have tried directly from the DB query using CTE, I have tried doing the joins from PBI, and also combining the queries, but no attempt has worked. I can´t join the extras with the material field because in some cases the id doesn´t exist in both tables.

Any advice or guidance will be of great help, thank you in advance.

[v-robertq-msft]

Hi, @LuisVillarreal 

It seems that the first picture you uploaded has been expired for some reason, you should check.

According to your description, it seems that you want to get the column value from an unrelated table, I think there is still a way to possibly achieve this, that is using DAX to create calculated columns.

Maybe you can upload some sample test data (without sensitive data) instead of some sample pictures so that I can help you to check if it’s possible to be achieved in the Power BI.

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LuisVillarreal]

Hi @v-robertq-msft thank you for been interested in this case, here is the link for the MWE with sample data: pbi 

Please let my know if you can download it. Thank you in advance! Regards.