Forum Discussion
RELATED() doesnt work corectly
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."
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
2 Replies
Alf94Solution Supplier
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,
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