How to replicate Power BI vlookup?

Syntax

The syntax for the Power BI vlookup can be overwhelming to look at but once you know what all the parameters represent, it becomes very easy to grasp.

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

Result column is the column that you want to retrieve the value from, search column is the column name that you want to search through, search value is a scalar value that you want to match in order to get the result and the alternate result is an optional parameter that lets you deal with error, indistinctive values and zeros.

Example

The function is simple and straight-forward, but an example will help shed the light on exactly how powerful and useful this function could be when building out dashboards in Power BI.

Suppose you have to compare the actual sales of a company with a forecasted value. In one table you have forecast and in another, you have the actual sales and you want to match them using a lookup.

You have these two tables to work with, but you want to get the actual sales data in the expected table for comparison. This is how you can write a query for the expected results:

Forecast = LOOKUPVALUE (Actual [Actual Sales], Actual[Company],Expected[Company])

This is pretty simple to attain. You can also incorporate the alternate value in this function. For instance, let us assume that VC3 and G4 do not have any sales, hence we do not have them in the actual sales table. Using lookup will retrieve BLANK () value but what if we wanted to replace it by a default value. This is how we can make a minor change in the above query to attain that.

Forecast = LOOKUPVALUE (Actual [Actual Sales], Actual[Company],Expected[Company],999)

Now the lookup function is powerful because it can have multiple criteria. For example, if we have different forecasts and actual sales for each month, we would still be able to use this function.

Considering the tables above, let us try to get the forecasts vs actual comparison using the formula above. The result is going to be something strange and unwanted because we are not using the month as a matching criterion.

This result can be fixed by using month and company name as criteria in the lookup function. Here is how you can fix it.

Forecast = LOOKUPVALUE (Actual [Actual Sales], Actual[Company],Expected[Company],Actual[Month],Expected[Month],999)

The result will look something like this which is what we wanted to get.

Summary

The lookup function can come in handy for any searches that you may need to perform. This function can help you become a power user of DAX and transform your future dashboard!

For more useful blogs, please visit Learn DAX