Get a field value from a related table in Power BI: DAX RELATED Function Explained

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
Get a field’s value from another table in Power BI using DAX related function

Sometimes, in Power BI, you need to access a field’s value from another table that somehow is related to the existing table. You can use Power Query transformations such as combining Merge with something else. However, this can be needed when you write a DAX expression too. In this article and video, I explained a simple but effective DAX function for this purpose; RELATED.

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 my sample has the relationships as below. It is not a star-schema, but it serves the purpose of explaining the RELATED function.

Power BI sample model with relationships

In other view, here are the relationship details in the model above;

The relationship key columns in the sample Power BI model

The Related is a very simple to use function in DAX. This function is a scalar function (It returns only one single value), and gets one single input parameters. The result would be the value from that field in the other table based on the relationship already exists in the model.

RELATED(<column>)  

The only input parameter for this function is the name of the column which we want to fetch the value of it. Let’s just see that as an example.

Let’s say I want to add a column in the DimProduct table showing the EnglishProductSubcategoryName. However, the EnglishProductSubcategoryName exists in the DimProductSubcategory table. If I add a column and try to write the expression below, it won’t work.

EnglishProductSubcategoryName - wrong way = DimProductSubcategory[EnglishProductSubcategoryName]

The expression above won’t work, and I will get an error, saying that:

A single value for column ‘EnglishProductSubcategoryName’ in table ‘DimProductSubcategory’ cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

You cannot access a field’s value from another table in a calculated column

Why you can’t write an expression that way? Because the EnglishProductSubcategoryName in the other table has multiple values, not one single. Your column expression should return one single value. The EnglishProductSubcategoryName that is for this product (the current row’s product). You can use something like a LookupValue function in DAX to retrieve the value you want, but the solution is much simpler than that using the RELATED function.

Fortunately, in the model there is a relationship between the two tables based on ProductSubcategoryKey;

The existing relationship between the two tables

This means that the RELATED function can give you the value of any column from the DimProductSubcategory table, while you are writing a calculated column in the DimProduct table. All you need as an input is the name of the column you want to pull the data from it.

Sub category = 
RELATED(DimProductSubcategory[EnglishProductSubcategoryName])
The Related function fetches the value from another table based on the existing relationships in the model.

The Related function goes through a one-to-many relationship, and will give you a value from the ONE side of the relationship and bring it to the MANY side.

How the RELATED function works in Power BI and DAX

The Related function does not only travel through one relationship. It can go through all the relationships (as long as it follows the rule of returning one value to the main table, which means it travels towards ONE sides of relationships). You can use the same approach to get the EnglishProductCategoryName column from the DimProductCategory table, even though there is no direct relationship between DimProduct and DimProductCategory.

Category = 
RELATED(DimProductCategory[EnglishProductCategoryName])

The result is the category name in the product table as another column;

The related function accesses the field’s value from tables even if the relationship is not direct

In the example above the values of category names travelled through two relationships, with just one mention of the RELATED function.

The related function can traverse multiple relationships

As you see the Related function makes things far simpler than LookupValue if the relationship already exists. There is, however a direction that the RELATED function won’t work on that.

The Related function pulls the values from the ONE sides of the relationship to the other side. This means one-to-one relationship and one-to-many relationship works just fine with this function. But not the many-to-many relationship.

Also, the one-to-many relationship only allows you to use the RELATED when you are on the MANY side of the relationship, not the ONE.

The reason for all of these is that the result of the RELATED function is just one value. When you want to use it on the MANY sides, then multiple values are returned. The RelatedTable can be used in those scenarios, which I will explain later.

In what situations the related function in DAX doesn’t work.

You can use the Related function in the measures when necessary. Here is an example, in the example below I am using SUMX to get the sum of sale for products with the color of Red. The column that I am calculating the sum of it is in the FactInternetSales table, and the Color is in the DimProduct table.

Sum of Sales for Red products = 
SUMX(
    FILTER(
        FactInternetSales,
        RELATED(DimProduct[Color])="Red"
    ),
    FactInternetSales[SalesAmount]
)

There are, of course, much easier ways to write the expression above using Calculate. However, I just wrote it using SUMX without the help of extra measure to show you how the RELATED function can work in this context. I have filtered the FactInternetSales table using the Color field in the DimProduct table using the RELATED function used inside a FILTER.

The Related function can be used in a calculated column or a measure

Summary

The Related function in DAX can be used to fetch a value from a field of another table. However, that table should be related to the existing table somehow in the model. The relationship should be in a way that it returns one value from that table per value in the main table. The Related function can traverse multiple relationships in the model and can be used also inside measures or other functions.

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