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:
- 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 my sample has the relationships as below. It is not a star-schema, but it serves the purpose of explaining the RELATED function.
In other view, here are the relationship details in the model above;
RELATED DAX Function
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.
Sample of using the RELATED
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.
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;
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 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.
The RELATED can traverse multiple relationships
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;
In the example above the values of category names travelled through two relationships, with just one mention of the RELATED function.
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.
When the RELATED doesn’t work?
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.
Using Related in Measures
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.
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.
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.