Power BI: How to Replicate Vlookup & Lookup Value From Another Table

You would want to lookup a value in a table from another table when there isn’t a direct relationship between the two tables. We may need to lookup values either for a measure or for a new column added to our existing table.

There are mainly five ways to get values from one column to another column in Power BI:

1. LOOKUPVALUE() function
2. CALCULATE() function
3. RELATED() function
4. ADDCOLUMNS() function
5. Merge Queries

We’ll cover LOOKUPVALUE() function in detail in this post. The benefit of using LOOKUPVALUE is that you can get the values even if you don’t have a relationship between the tables. The downside is that the function makes your report very slow especially if there are many rows in your table.

How to Replicate Vlookup in Power BI?

VLOOKUP equivalent in Power BI is the DAX function LOOKUPVALUE(). The function will lookup the value in a another table and return the row value that meets all the conditions. Here is the syntax:

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

Term	Definition
<result_columnName>	The value you want returned (the column name)
<search_columnName>	Column to match the value from the lookup table to the table getting the results
<search_value>	Column from result table to match with <search_columnName> in lookup table
<alternateResult>	Is Optional to use. Whenever a match was not found, then you can choose what should show up in the result row. So, if there’s nothing returned, the row will be blank, you can choose to show “None” or any other expression

Here is an example explaining the different parts of the LOOKUPVALUE function. We’ll match customer ID from each table to get the email returned as the lookup value.

LOOKUPVALUE Error – “A table of multiple values was supplied where a single value was expected”

This error will show up whenever you use the LOOKUPVALUE() function where values are repeated or duplicate values exists. In our case, we have the same customer ID repeated in the lookup table so multiple email values are sent back as the result.

In this case LOOKUPVALUE() function will not work and it is better to use CALCULATE() function with filters

LOOKUPVALUE Returns Blank Value

Whenever the LOOKUPVALUE function returns blank value, it means that it couldn’t find it in the lookup table. In that case, you can set another value to be returned whenever there is no value returned.

According to the syntax below, set the <alternateResult> to any string literal, expression or other column values to be returned.

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

Here’s more details about LOOKUPVALUE function.