RELATEDTABLE – assigning of table connected by relation (DAX – Power Pivot, Power BI)

The RELATEDTABLE function assigns values from the associated table. But while RELATED takes values from the “short” table to the “long” one (from the “number one” site of the relation to the “star” one) , RELATEDTABLE goes backwards.

Therefore, while the RELATED output is one unique value, the output of the RELATEDTABLE function is a set of values. To calculate it in a column, the output must be combined with some other function.

It sounds complicated, but it’s not that terrible. Let´s demonstrate it on this example.

In the first table there are sales in our branches, which are in different countries. In the other table, there are countries, and we want to summarize the total revenue there.

Lets upload the data to Power BI and create the relationship.

Write this formula to a new column in a Dimension table:

Total revenue for this country= SUMX(RELATEDTABLE(Items);Items[Revenue])

RELATEDTABLE assigns the proper table and SUMX calculate the values.