There are multiple functions that can help when you work with tables that are connected through relationships. One of these functions is Relatedtable. This function gives you the subtable from the other table for all the rows related to the current row. For example, all sales transactions (from the Sales table) for the current customer (from the Customer table). In this article and video, I explain how this function works.
Video
Understand the relationship
Before I talk about the function itself, I want to emphasize the need to understand how the relationship in Power BI works. I have written many blog articles (and videos) about this subject, here are some of those:
- What is a Relationship?
- What is the Cardinality of the Relationship?
- What is the Direction of the Relationship?
- Inactive relationship and what to do about it?
The model I am using in this sample has the relationship as below.
RELATEDTABLE DAX Function
I have previously explained what is the RELATED function in DAX and how you can use it. Similar to that function, the RELATEDTABLE function also works with the relationship. However, unlike the other function, the RELATEDTABLE returns a table as the output. That makes the RELATEDTABLE function a tabular function. The returned table is a sub-table of the given table for all records that match the key in the current row of the current table. This is how the function works;
RELATEDTABLE(<tableName>)
The input table can be a table in your dataset, let’s say FactInternetSales. If this function is run while we are at the row context of the DimCustomer table, the output will be all sales transactions are related to that specific customer. Let’s see that through an example.
Sample of using the RELATEDTABLE
Let’s assume that we want to add a calculated column to the DimCustomer table and show the total sales amount for that customer. This is what the DimCustomer table looks like;
There are different ways to calculate the total sales amount for each customer, but I want to show you a method that uses the RELATEDTABLE so that you can learn how to use this function.
In order to calculate the sales amount for each customer, we need to first find all transactions for each customer. The sales transactions are in the FactInternetSales table, and there is a relationship between the two tables.
We need a way that gives us the rows from the FactInternetSales for each record in the customer table. Something like below;
The RELATEDTABLE function will travel through the existing relationship between tables, and will populate a list of rows (sub-table) from the give table (in the case above; FactInternetSales) for each row in the table that we call the function from it (in this case; DimCustomer).
This means our calculation can be like this:
Sales =
SUMX(
RELATEDTABLE(FactInternetSales),
FactInternetSales[SalesAmount]
)
In the example above, the RELATEDTABLE generates a table that is used as the input of the SUMX function. Of course, the RELATEDTABLE can be used in many other functions that expects a filter or a table expression, such as Calculate too.
The RELATEDTABLE function can traverse multiple relationships
Very similar to the RELATED function, the RELATEDTABLE function can traverse through multiple relationships. For example, Let’s say that I have a third table in the model; DimGeography. The DimGeography is related to DimCustomer.
I can use the same calculated column expression to get the total sales for each geography area;
The RELATEDTABLE function can be also used in measures in a similar way. This function cannot be used directly in a measure because it returns a table. You need to wrap it inside another function that accepts a table or table expression. I have written an example of such usage in this article.
Summary
The RELATEDTABLE function is working with existing active relationships in the model. This function returns a table, which is the subset of rows from the given table for the row context of the other table. This function can traverse multiple relationships. This function can be used in measures too, but as this is a tabular function, you need to wrap it in other functions to return a scalar value. If you like to know more about the RELATED function, read this article.
Download Sample Power BI File
Download the sample Power BI report here:
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.