Adding values to a sum between tables

[Lukaszj]

Hello,

I have a dataset that contains two tables that connect to a fact table. Table 1 - Project Information with a One-to-Many relationship, and Table 2 - Project Information with a One-to-One.

I am trying to show, for any given project, the total Svgs/Loss amount + that project's ReimbursementLabor  - CosttoCompleteLabor. I thought this would be simple but I seem to be unable to create a measure between the two tables. Is there any other way I should be approaching this or anything I'm missing?

Any guidance would be greatly appreciated. Thank you!

[jgeddes]

With that relationship set up as long as you are not using the ID column from Archive Labor as the visual filter you should be able to get your measures to calculate across tables, but I would have to see the measures being used to understand why they are not working for you.

[Lukaszj]

Would a measure be the best way to calculate this? I'd like to display the final value on a card.

When I try to create the measure, the tblReimbursement columns don't "auto-fill" while typing which is what's leading me to believe theres an issue. Is that inherently indicative of a data issue or might I just have to manually type in all the 'Table'[Column] syntax?

[jgeddes]

A measure would be the best option for your need here.

I have had some issues with "auto-fill" lately personally. I would type the 'table'[column] and see what the result is rather than assuming auto fill is working. 

If you need a hand writing the measure, let me know which table/columns you are wanting to use and I can assist.

[Lukaszj]

Thanks for the tip! I was able to write the measure below and it seems to give the desired output and works when I filter for different project IDs. Would you have done it a different way?

[jgeddes · Accepted solution]

That looks like something I would have written. Glad it is working.

The only thing I would tend to do differently is use variables when I have measures with multiple calculations in them. It allows me to easily test the components of a measure to find which parts work and which parts are "broken".

[jgeddes]

My first suggestion would be to review the cross filter directions of the existing relationships. It sounds like one of the product ID columns is trying to filter a table that it cannot.

[Lukaszj]

Here is a snippet of just the related tables. The only one I can edit is the one-to-many with Archive Labor, but I didn't notice any difference when changing that one between single and both.