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?
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 and LOOKUPVALUE are working similarly to LOOKUP function in Excel.
The RELATED DAX function returns the related value from one table to another table when the tables are related.
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.
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
RELATEDis a single value that is matched and related to the current row.
To can use RELATED() function, you must ensure the following:
- You must create a relationship to can use the RELATED function.
- The RELATED function requires that a relationship exists between the current table and the table that has the related information.
- The RELATED function works in both directions if you have a one-one relationship.
- The RELATED function works in one direction if you have a Many-One relationship.
- The RELATED function can only be used in the Fact table if you have a Many-One relationship.
- 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().
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:
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.“!
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:
- You can’t use the RELATED function if the tables are not related.
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.
- The RELATED function will not work if the relationship is not active.
Solution
- Double click on the relation arrow to edit the relationship.
- Check “Make this relationship active“, then click Ok.
- The RELATED function cannot be used if you have a many-many relationship or limited relationship.
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.
- The RELATED function will not work if the relationship is not established properly with the correct column.
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.
- The RELATED function can’t be used in Dimension table (Lookup table) if the current established relationship is one-many.
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).
- The RELATED function can’t be used directly in a Measure expression.
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.
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)])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.
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.
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!
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.
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:
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.
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.
As you can see in the below image, Both RELATED and LOOKUPVALUE are returned the same result!
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
