Power BI DAX RelatedTable Function: Get the subtable related to the current row

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
Power BI DAX RELATEDTABLE function learn how it works

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:

The model I am using in this sample has the relationship as below.

sample data model

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;

Sample data of the DimCustomer table

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;

how the RELATEDTABLE function works in DAX and Power BI

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.

RELATEDTABLE function used in a calculation in DAX and Power BI

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.

A model with multiple relationships

I can use the same calculated column expression to get the total sales for each geography area;

The Relatedtable function traverse multiple relationships

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:

    Enter Your Email to download the file (required)

    FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
    Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
    Reza Rad
    Trainer, Consultant, Mentor
    Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
    His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
    He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
    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.

    Leave a Reply