Tutorials

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.

2 Comments

Ajay says:

3.12.2018 at 14:32

Hi ,
I am new to power BI,
How to use Sumx(relatedtable()) for String data type
Please give a solution for this
Olga says:

11.3.2019 at 15:49

I think the function VALUE(text) should be used to convert the string values to numeric data type, and after this the aggregate function SUMX can be used with RELATEDTABLE function.

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.