RELATED vs LOOKUPVALUE in DAX: How and when to use them in Power BI

By: Kenneth A. Omorodion   |   Updated: 2022-05-06   |   Comments   |   Related: > Power BI

Problem

One very common question I have been asked by Power BI newbies (particularly those with Excel background) is how they can do the popular VLOOKUP in Power BI using DAX. I have always pointed them to two DAX functions that can be used to achieve this easily including the RELATED and the LOOKUPVALUE DAX functions.

But from experience, I have seen myself ending up having to write out the DAX for business users as there are some aspects of using these functions that may make them not work in certain situations. Hence, this is the reason why I had to write this tutorial to explain in simple terms how and when to use these DAX functions to perform a lookup in your SQL Server data.

Solution

Before I demonstrate how to use these DAX functions, lets first summarize each of these DAX functions.

DAX RELATED Function

This DAX function returns a related value from another table when it's used as a lookup function. While using this DAX function it is important to understand the following items:

• For this function to work it is important to ensure that there is a relationship created between the current table and the table with the related information you are performing a lookup on.
• The existing relationship must be a Many-to-One type with the many sides being the table you are bringing the values you have looked up into.
• The function requires a row context; thus, it can only be used in calculated column expressions.
• You cannot use this function to perform a lookup if your cardinality is a Many-to-Many or if your model is a Composite model.

The syntax for the RELATED function is very simple as seen below. It only requires a single parameter which is a column. The column parameter requirement refers to the column in the other table that you want to retrieve into the main table. The output is a single value that is related to the current row.

= RELATED(<column>)

Let's look at a simple example of how to use the DAX RELATED function. Let's say we have the following three datasets

What we are trying to achieve in this demo is to lookup for "Category" in "Dataset 1" into "Dataset 2" and return the "Value" column in Dataset 1 as it does not exist in Dataset 2. In summary, what we are trying to achieve is as seen in the diagram below.

This can be easily achieved using the RELATED function since the relationship between Dataset 1 and Dataset 2 is a One-to-Many type as seen below.

We can thus use the RELATED function as seen below.

= RELATED (From Dataset 1) = RELATED('Dataset 1'[Value])

The result of this is as seen in the diagram below.

Let's also look at a condition where we have a Many-to-Many relationship between datasets as seen between "Dataset 2" and "Dataset 3". The relationship diagram between these datasets is as seen below.

So, if we must retrieve the "Value" column from Dataset 3 row by row into Dataset 2 i.e., do a lookup, we would get the error as seen below.

As you would recall, we are clear on this that we cannot use the RELATED function where the relationship between the tables is a Many-to-Many type as in this case, hence the reason for the error message. But in your case, you might just happen to have a cardinality which only exists in a Many-to-Many, thus you are unable to use the DAX RELATED function. Don't worry, there is a solution to that, that the purpose of this article. Just keep reading!

DAX LOOKUPVALUE Function

The DAX LOOKUPVALUE function returns the value for the row that meets all specified criteria looking at one or more search conditions. There is some further information I would recommend you should read about LOOKUPVALUE function which I have not detailed in this article. Please see the "Next Steps" section of this article.

In summary, while using the DAX LOOKUPVALUE function it is important to understand the following remarks:

• Consider using this function to do a lookup where the cardinality of the tables is not a Many-to-One type
• Avoid using the ISERROR or IFERROR functions to capture errors returned by this function
• It is not supported in Direct Query mode when used in RLS rules or calculated columns

The syntax of the DAX LOOKUPVALUE function is more complex than that of the DAX RELATED function as can be seen below.

= LOOKUPVALUE(
    <result_columnName>,
    <search_columnName>,
    <search_value>
    [, <search2_columnName>, <search2_value>]…
    [, <alternateResult>]
)

There are three mandatory parameters and some other optional ones in the above syntax.

The mandatory parameters include:

• result_columnName: This parameter refers to the name of the column that you wish to return. In the scenario we used for the three datasets above, this would be the "Value" column from Dataset 3.
• search_columnName: This parameter refers to the name of the column from where you are match your string or keyword. In the example above, we are matching and searching on the "Category" column in Dataset 3.
• search_value: This refers to the name of the value or column which you are using as lookup value in your search. In the example above, we are trying to search for "Category" in Dataset 2 from Dataset 1.

Let's now see how we can use this function in situations where RELATED would not work, for example where we have a Many-to-Many relationship between the tables. The syntax for using the LOOKUPVALUE function is as seen below.

LOOKUPVALUE (From Dataset 3) = LOOKUPVALUE ('Dataset 3'[Value], 'Dataset 3'[Category], 'Dataset 2'[Category])

The result of this is as seen in the diagram below.

In general, using the example described in this article, the DAX LOOKUPVALUE function can be interpreted as follows. Since the "search_value" parameter of the function is first evaluated before the "search_columnName" and the "result_columnName" parameters, you can interpret the example like "lookup the "Category" value or column in Dataset 2 from Dataset 3, and where it matches do return "Value" from Dataset 3".

In summary, in this article, I have used some advanced terms which are beyond the scope of this article, I would like to encourage you to read more about these as listed in the "Next Steps" section. I am sure by now after reading this article, you should now be sure of what to do when you need to do a lookup in DAX.

Next Steps

• Get more information about the DAX RELATED function from the Microsoft documentation.
• Get more information about the DAX LOOKUPVALUE function from the Microsoft documentation.
• Read more about Many-to-many relationships in Power BI.
• Get more information about Composite Models.
• Read more about Limited Relationships as it affects the DAX RELATED function.
• Try this tip out in your own data as business requires.

Learn more about Power BI in this 3 hour training course.

About the author

Kenneth A. Omorodion is a Business Intelligence Developer with over eight years of experience. He holds both a bachelor’s and master’s degree (Middlesex University in London). Kenneth has the MCSA, Microsoft Data Analyst - Power BI and Azure Fundamentals certifications. Kenneth is a Microsoft Certified Trainer and has delivered corporate training on Power BI, SQL Server, Excel and SSRS.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips