Use Different Tables Data In a DAX Column

[srpeters]

Hello,

I have three different tables with three different amounts. Two of these tables have different keys and the third table has both of these keys. I also have a fourth table with one of the keys and a description of what it correlates to. Here is some sample data:

Table 1:

Table 2:

Table 3:

Table 4:

As you can see I am getting an error message when trying to use DAX to subtract the three different tables amounts from each other in Table 3s DAX Column.

Below is the same DAX but as a measure:

Is it possible to replicate this measure for each row as a column in a table rather than a measure?

Thank you in advance!

[Greg_Deckler]

@srpeters You can use MAXX(FILTER(...), ...) or LOOKUPVALUE to return data in other tables when no relationship exists.

[srpeters]

Since I do have a relationship between these tables through the keys, shouldnt RELATED work? 

My relationships:

If not, then how would you format the LOOKUPVALUE for 3 different tables?

Thank you!

[Greg_Deckler · Accepted solution]

@srpeters Actually in looking at this closer, I believe the issue is that you are using RELATED to reference the same table that you are in, Table3. When creating a calculated column in Table3 that uses a column in Table3, just reference the column, [Column], you do not need to use RELATED and it actually won't work anyway and you will get the error you are receiving.

[srpeters]

I see now that the RELATED() function does not work for limited relationships. Since the data I am working with has many-to-many relationships, I guess I need to use LOOKUPVALUE or MAX(FILTER()). 

Thank you for your help!

[Greg_Deckler]

@srpeters Right, RELATED works on the many side of a 1-to-many relationship. Might be able to use RELATEDTABLE along with an X-aggregator.