You are currently viewing Power BI: RELATED Vs LOOKUPVALUE DAX
RELATED Vs LOOKUPVALUE DAX

Power BI: RELATED Vs LOOKUPVALUE DAX

In this post, we’re gonna explain what’re the main differences between RELATED Vs LOOKUPVALUE DAX, and When you should use RELATED DAX function? as well as When you should use LOOKUPVALUE DAX function? and Which DAX function is better in terms of performance?


RELATED Vs LOOKUPVALUE DAX in Power BI

Both RELATED and LOOKUPVALUE are DAX functions that are used in a calculated column when you need to reference a column from another table to return a value that is related and has an exact match to the current row.

RELATED Vs LOOKUPVALUE DAX
RELATED Vs LOOKUPVALUE DAX

RELATED and LOOKUPVALUE are working similarly to LOOKUP function in Excel.

What’s RELATED DAX Function?

The RELATED DAX function returns the related value from one table to another table when the tables are related.

use the related function with one-one relationship tables in Power BI

As you can see in the above example, we have two tables that are related, so in this case, we can reference a column from one table to another using the RELATED function.

RELATED DAX Function Syntax

In a calculated column, you just need to type RELATED( then if everything is OK the auto-intelligence complete will suggest the column names list to select your column as an argument for the RELATED() function.

RELATED(<column>)

Note: The returned value of RELATED is a single value that is matched and related to the current row.

When to use RELATED DAX function in Power BI?

To can use RELATED() function, you must ensure the following:

  1. You must create a relationship to can use the RELATED function.
  2. The RELATED function requires that a relationship exists between the current table and the table that has the related information.
get column value from another table using related in Power BI | Power Platform Geeks
  1. The RELATED function works in both directions if you have a one-one relationship.
use the related function with one-one relationship tables in Power BI
  1. The RELATED function works in one direction if you have a Many-One relationship.
  2. The RELATED function can only be used in the Fact table if you have a Many-One relationship.
use related with one to many relationship in Power BI
  1. The RELATED function can only be used in a calculated column expression where the row context is clear or as a nested function in an expression that uses a table scanning function like SUMX().
use the related function in a column in Power BI

Why RELATED DAX function is not working in Power BI?

In some cases, the RELATED function maybe not working as expected, and the IntelliSense of RELATED function doesn’t show the suggested related columns as shown below:

the IntelliSense of RELATED function doesn't work in Power BI

In this case, if you tried to provide the correct column name manually, you will get this error “The column either doesn’t exist or doesn’t have a relationship to any table available in the current context.“!

related dax function is not working in Power BI

So in such case, to get the RELATED function work as expected and the IntelliSense of RELATED function show the suggested related columns, you should first be aware of the following main reasons:

  1. You can’t use the RELATED function if the tables are not related.
use related function with two unrelated tables in Power BI

Solution

  • Click on the “Model” section, then from the above ribbon.
  • Click on “Manage Relationships” to show all available relationships.
  • Then click on the “New” button to create a new relationship between the two tables.
manage relashionship in Power BI
  1. The RELATED function will not work if the relationship is not active.
related function is not working in Power BI

Solution

  • Double click on the relation arrow to edit the relationship.
  • Check “Make this relationship active“, then click Ok.
make relashionship active in Power BI
  1. The RELATED function cannot be used if you have a many-many relationship or limited relationship.
use related function with many to many relationship in Power BI

Solution

Actually, it’s one of the limitations of RELATED function, and you can only use it with one-one or one-many relationships, and there is no workaround to do this.

  1. The RELATED function will not work if the relationship is not established properly with the correct column.
fix incorrect relationship in Power BI

Solution

Actually, this kind of error will not work and you will not be able to save your relationship till you use the correct column. but in some cases, the relationship will be established technically but will not work as expected!

So to solve this issue, you just need to make sure that the relationship has been configured correctly with the target key column name.

  1. The RELATED function can’t be used in Dimension table (Lookup table) if the current established relationship is one-many.
use related function in the lookup table in Power BI

Solution

As we earlier mentioned, the RELATED DAX function works in one direction if you have a Many-One relationship, and you can use it in the Fact table, not in the Lookup Table.

So in this case, you should use the RELATED function in the Fact table (Product List) to get it to work, and there is no way to use it in the Dimensions Lookup Table (Product Price).

use related with one to many relationship in Power BI
  1. The RELATED function can’t be used directly in a Measure expression.
use the related function in the measure in Power BI

Solution

The RELATED function is evaluated within row context, which means the current row. so it should be used in the Calculated Column where the row context is clear.

use the related function in a column in Power BI

So in your Measure, if you are using the CALCULATE function that removes the row context, you will not be able to use the RELATED function because the row context is no longer available, and in this case, you have to use the filter expression to define the row context and to be able to use the RELATED function as below:

Measure = calculate( sum('Product List'[Sold]),FILTER('Product List', RELATED('Product Price (RELATED)'[ProductKey]) = 1 ))

Also, you can create a calculated column that holds the related value,

Calculated Column(RELATED) = RELATED('Product Price (RELATED)'[Price])

Then simply, you can use this column in your Measure as below:

Measure = sum('Product List'[Calculated Column(RELATED)])

RELATED DAX Function Example in Power BI

Consider, you need to create a DAX formula for a calculated column to get data from another column in another table which has a relationship with the current table as shown below.

get column value from another table using related in Power BI

So in this example, we need to get the Price field from another table 'Product Price', and then wen meed multiply the returned value to a Sold column in the current table 'Product List'!

In this case, you should use the RELATED function as the following:

Total = 'Product List'[Sold] * RELATED('Product Price'[Price])

Output

The output of RELATED function should be a single value that is related to the current row as shown below.

RELATED DAX Function Example in Power BI

What’s LOOKUPVALUE DAX Function?

The LOOKUPVALUE DAX function also returns the related value from another table to the current table. but in this case, the two tables are not related and you can’t establish a relationship between them!

get value from another table when you can't create a relationship in Power BI

As you can see in the above example, we have two tables that are NOT related! so in this case, we can’t use the RELATED DAX function, and instead, we should use the LOOKUPVALUE DAX function to reference a column from another table when you can’t create a relationship between the two tables.

LOOKUPVALUE DAX Function Syntax

Actually, the LOOKUPVALUE syntax is more complicated than the RELATED function, where you should provide multiple arguments to get LOOKUPVALUE work, Unlike the RELATED function, you just need to provide only one argument (The related column) to get it to work.

Below is the LOOKUPVALUE DAX Function Syntax

LOOKUPVALUE(
    <result_columnName>,
    <search_columnName>,
    <search_value>
    [, <search2_columnName>, <search2_value>]…
    [, <alternateResult>]
)
Term Definition
result_columnName The name of an existing column that contains the value you want to return. It cannot be an expression.
search_columnName The name of an existing column. It can be in the same table as result_columnName or in a related table. It cannot be an expression.
search_value The value to search for in search_columnName.
alternateResult (Optional) The value returned when the context for result_columnName has been filtered down to zero or more than one distinct value. When not provided, the function returns BLANK when result_columnName is filtered down to zero value or an error when more than one distinct value.

When to use LOOKUPVALUE DAX function in Power BI?

As we earlier mentioned, the LOOKUPVALUE function is used when you can’t create a relationship between two tables.

So in this case, to can reference a column from another table, you must use LOOKUPVALUE DAX function.

get column value from another table using lookupvalue function in Power BI

Note: The LOOKUPVALUE function is not supported in DirectQuery mode when used in calculated columns or RLS rules.

LOOKUPVALUE DAX Function Example in Power BI

Consider, you need to create a DAX formula for a calculated column to get data from another column in another table that are not related as shown below:

get column value from another table using lookupvalue function in Power BI

So in this example, if you need to get the Price field from another table 'Product Price' that are NOT related, you should use the LOOKUPVALUE function as the following:

LOOKUPVALUE = LOOKUPVALUE('Product Price (LOOKUPVALUE)'[Price],'Product Price (LOOKUPVALUE)'[ProductKey],'Product List'[ProductKey])

Now we need to multiply the returned value to the Sold column in the current table 'Product List', so we should use the below formula.

Total (LOOKUPVALUE) = 'Product List'[Sold] * LOOKUPVALUE('Product Price (LOOKUPVALUE)'[Price],'Product Price (LOOKUPVALUE)'[ProductKey],'Product List'[ProductKey])

Output

The output of LOOKUPVALUE function should as below.

lookupvalue example  in Power BI

Note: If there’s no match that satisfies all the search values, BLANK or alternateResult (if provided) will be returned. In other words, the function won’t return a lookup value if only some of the criteria match.

Which DAX function is better for performance, RELATED or LOOKUPVALUE?

As you can see in the below image, Both RELATED and LOOKUPVALUE are returned the same result!

RELATED Vs LOOKUPVALUE DAX Comparison
RELATED Vs LOOKUPVALUE DAX Comparison

However, for better performance, it’s more efficient to use the RELATED function rather than the LOOKUPVALUE function if you CAN establish a relationship between the two tables.


Conclusion

If you would like to reference a column from another table to the current table, you should use RELATED DAX function or LOOKUPVALUE function.

Use RELATED function if the current two tables have already a relationship, otherwise, you should use the LOOKUPVALUE function if you can’t create a relationship between the two tables.

Download

Download the PIBX file from GitHub at RELATED VS LOOKUPVALUE Example in Power BI

See Also

Mohamed El-Qassas

I'm Microsoft MVP, SharePoint StackExchange Moderator, Microsoft QnA Moderator, C# Corner MVP, Microsoft TechNet Wiki Judge, and Senior Technical Consultant with +12 years of experience in SharePoint, Project Server and Power Platform.