topic Re: Use Different Tables Data In a DAX Column in DAX Commands and Tips

Use Different Tables Data In a DAX Column

srpeters — Mon, 25 Mar 2024 13:51:57 GMT

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!

Re: Use Different Tables Data In a DAX Column

Greg_Deckler — Mon, 25 Mar 2024 15:10:05 GMT

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

Re: Use Different Tables Data In a DAX Column

srpeters — Mon, 25 Mar 2024 15:50:51 GMT

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!

Re: Use Different Tables Data In a DAX Column

Greg_Deckler — Mon, 25 Mar 2024 16:05:24 GMT

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.

Re: Use Different Tables Data In a DAX Column

srpeters — Mon, 25 Mar 2024 17:24:38 GMT

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!

Re: Use Different Tables Data In a DAX Column

Greg_Deckler — Mon, 25 Mar 2024 17:29:43 GMT

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.