RELATED() doesnt work corectly

[Anonymous]

Hello, I have a problem to write correctly DAX formula.
I have two tables:

Table1 with fields [t1_a], [t1_b], [t1_c]
Table2 with fields [t2_a], [t2_b], [t2_c]

Between these two tables is a active many to many relationship ([t1_a]->[t2_a]).
If I want to show all fields from these two tables in a one table - its possible.
But I want to create in Table1 a new column, that contain value [t2_c] (related per relationship) from Table2 .
If I use RELATED() its doesnt work.

Column = RELATED(Table2[t2_c])

return: "The column 'Table2[t2_c]' either doesn't exist or doesn't have a relationship to any table available in the current context."

[Anonymous · Accepted solution]

Hi  @Anonymous ,

I created some data:

Table1:

Table2:

Here are the steps you can follow：

1. Create calculated column.

Use the Lookupvalue() function

Column =
LOOKUPVALUE(
    'Table2'[t2_c],
    'Table2'[t2_a],'Table1'[t1_a],'Table2'[t2_b],'Table1'[t1_b])

LOOKUPVALUE function (DAX) - DAX | Microsoft Learn

Or use the SumX(), Maxx() functions to get it

Column 2 =
MAXX(
    FILTER(
       ALL(Table2),
        'Table2'[t2_a]=EARLIER('Table1'[t1_a])&&
        'Table2'[t2_b]=EARLIER('Table1'[t1_b])),
        'Table2'[t2_c])

SUMX function (DAX) - DAX | Microsoft Learn

MAXX function (DAX) - DAX | Microsoft Learn

2. Result:

Refer to:

Power BI: RELATED function doesn't show related columns - deBUG.to

Best Regards,

Liu Yang

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

[Alf94]

Hi @Anonymous,

The problem comes from your many-to-many relationship. The function RELATED() only works with many-to-one relationships. Otherwise, Power BI cannot determine which value to select. You can read more about this function here : https://dax.guide/related/

Best,