How the DAX Function RELATEDTABLE will work in Power BI

How to use DAX RELATEDTABLE Function in Power BI

The RELATEDTABLE Function evaluates a table expression in a context modified by the given filters. The RELATEDTETABLE function changes the context in which the data is filtered, and evaluates the expression in the new context that you specify.
This function is a shortcut for CALCULATETABLE function with no logical expression. 

The RELATEDTABLE Function also works like a RELATED Function.

Notes:

It works like a RELATED Function, but It returns a table with all the rows related with the current one, and also it works from One-Side as well as from Many-Side in One-to-Many relationship Models.

It works on the Models with a One-to-One Relationship and Many-to-One Relationship as well.

In case of One-to-One Relationships, It can be used in the new Column/Measure defined on any One-Side table to perform the Lookup & retrieve the values from other Related table and vice versa.

But in case of Many-to-One / One-to-Many Relationships, It can be used in a new Column /Measure defined on One-Side table to perform the Lookup & retrieve values from the Many-Side table and vice versa.

Basic Syntax:

RELATEDTABLE(Table)

Now will discuss about the Functional behavior of the RELATEDTABLE Function in One-to-One and One-to-Many / Many-to-One relationships.

1) One-to-One Relationship:

Suppose we have two Tables with One-to-One Relationship in a Power BI Model as  follows.

For better understanding of the Relationship, I just added suffix as "_One" , tells that the tables have the One-to-One relationship.

Since both tables have the One-to-One relationship based on the key "Prod_Id", we can see the Unique rows for the Prod_Id in both the tables.

This means the column "Prod_Id" in one table has only one instance of a particular value, and the other related table has only one instance of a particular value.

Since there was One-to-One Relationship between Tables, we can use RELATEDTABLE Function to Calculate a new Column/Measure defined on any one of the One-Side table to perform the Lookup & retrieve the values from other Related table.

Defining a new Column and a new Measure on the 'SalesByCust_One' Table :

cTotalSales = SUMX(RELATEDTABLE(Products_One), SalesByCust_One[Units_Sold]*Products_One[Unit_Price])
mTotalSales = SUMX(RELATEDTABLE(Products_One), SUMX(SalesByCust_One, SalesByCust_One[Units_Sold]*Products_One[Unit_Price]))

Defining a new Column and a new Measure on the 'Products_One' Table :

c_TotalSales = SUMX(RELATEDTABLE(SalesByCust_One), Products_One[Unit_Price]*SalesByCust_One[Units_Sold])
m_TotalSales = SUMX(RELATEDTABLE(SalesByCust_One), SUMX(Products_One,Products_One[Unit_Price]*SalesByCust_One[Units_Sold]))

We were able to define the similar new Column and a Measure successfully on any one of the tables.

Note :
In real time, we should define them in any one of the Table, otherwise we may get a Circular reference error when try to define same calculated Columns with same reference on both the tables, in case of One-to-One Relationship.

Now we can see all these new Columns and Measures will returns the same values for rows but the grand Total value will be higher in case of Measure, which we will ignore that for now in this article.

Note:
To avoid the Grand Total Issue for Measures, in the above context use the below Formula:
mTotalSales = SUMX(RELATEDTABLE(Products_One), CALCULATE(SUM(SalesByCust_Many[Units_Sold]))*CALCULATE(SUM(Products_One[Unit_Price])))

m_TotalSales = SUMX(RELATEDTABLE(SalesByCust_Many), CALCULATE(SUM(Products_One[Unit_Price]))*CALCULATE(SUM(SalesByCust_Many[Units_Sold])))

2) One-to-Many Relationship:

Now lets make a Change in a Table "SalesByCust_One", by adding additional records with repeated Product_Ids, and will rename the Table name as "SalesByCust_Many".

Here, we will leave the "Products_One" table as it is without making any changes.

First go to Edit Queries and Select and Rename the Table to "SalesByCust_Many".

Next go to Query Settings > Applied Steps and then click on the Source gear icon.

Once click on the Source gear icon, it will open a window to update the Table.

Now add the more rows to the Table then click OK to apply and save changes to the Table.

When you Apply Changes and Closes the Query Editor window, Immediately we will see the following Error Message, as it is violating the One-to-One relationship already existed.

Now closing this Message Box, then go ahead and change relationship between the Tables from One-to-One to One-to-Many as follows..

Next say OK, then Apply changes.

Now In the One-to-Many / Many-to-One relational Model, we can see that RELATEDTABLE Function is working in the Calculations (new Column/Measures) made on the One-side as well as on the Many-side tables.

Finally we can Conclude that, in case of Many-to-One / One-to-Many Relationships, the RELATEDTABLE Function can be used in a new Column /Measure defined on One-Side table to perform the Lookup & retrieve values from the Many-Side table and vice versa.

A new Column and a new Measure on the 'SalesByCust_Many' Table :
cTotalSales = SUMX(RELATEDTABLE(Products_One), SalesByCust_Many[Units_Sold]*Products_One[Unit_Price])
mTotalSales = SUMX(RELATEDTABLE(Products_One), SUMX(SalesByCust_Many, SalesByCust_Many[Units_Sold]*Products_One[Unit_Price]))
A new Column and a new Measure on the 'Products_One' Table :
c_TotalSales = SUMX(RELATEDTABLE(SalesByCust_Many), Products_One[Unit_Price]*SalesByCust_Many[Units_Sold])
m_TotalSales = SUMX(RELATEDTABLE(SalesByCust_Many), SUMX(Products_One, Products_One[Unit_Price]*SalesByCust_Many[Units_Sold]))

Result:

Note:
To avoid the Grand Total Issue for Measures, in the above context use the below Formula:
mTotalSales = SUMX(RELATEDTABLE(Products_One), CALCULATE(SUM(SalesByCust_Many[Units_Sold]))*CALCULATE(SUM(Products_One[Unit_Price])))

m_TotalSales = SUMX(RELATEDTABLE(SalesByCust_Many), CALCULATE(SUM(Products_One[Unit_Price]))*CALCULATE(SUM(SalesByCust_Many[Units_Sold])))
Now we can see the same result for new Columns and new Measures as shown below :

Note :

The new Column "c_TotalSales" is defined on the One-Side table, which is returning the aggregated values ( eg: 4650=3600+1050 for the Prod_Id 1234 ) from the Many-Side table.

But the new Measures "m_TotalSales" or "mTotalSales" will returns the same values ( eg: 3600,1050 for the Prod_Id 1234 ) though they defined in either One-Side or Many-Side of the relationship tables.

-------------------------------------------------------------------------------------------------------- 

Thanks, TAMATAM ; Business Intelligence & Analytics Professional 

--------------------------------------------------------------------------------------------------------