powerbi - Accessing related values on the many side of a relationship

Question

Power BI table expansion happens only on the 1 side of the relationship.

Say table 1 is the m side and table 2 is the 1 side.

Due to the expasion we can use the corresponding value from the 1 side of the table in for example SUMX(table1, DIVIDE(table1[col1], realated(table2[col1]) )

If so then how does RELATEDTABLE formula work in case of m:1 and also in case of m:m relationship?

question from:https://stackoverflow.com/questions/66063938/accessing-related-values-on-the-many-side-of-a-relationship

Answer 1

Expanded table works from the many side of a relationship to the one side. In your example, table 1 expanded table contains table 2 but not vice-versa.

table 1 * <- 1 table 2

this is correct

SUMX(table1, DIVIDE(table1[col1], related(table2[col1]) )

RELATEDTABLE works from the one side to the many side and it returns the table with the rows corresponding to the one side

SUMX(table2, COUNTROWS( RELATEDTABLE(table1) ) )

the many to many relatioship in DAX are not a generic many to many relationship but some sort of "one to many at a higher level of granularity". In this case we have single direction relationships that from the point of view of the RELATEDTABLE function should behave like 1 to many relationships. RELATED instead cannot work since no single row can be returned.