LOOKUPVALUE in Power BI
Having said about the importance of the VLOOKUP function can we use the same function in Power BI is the common question from all the Power BI beginners, but unfortunately, we don’t have VLOOKUP Power BI instead we have similar kind of function i.e. LOOKUPVALUE function in Power BI. In this article, we will guide you through this function. If I have to tell you the importance of VLOOKUP in excel for all the excel users in simple words “it is just an integral part”. Yes VLOOKUP is an integral part of all the excel users and household functions in the Excel world.
What Does LOOKUPVALUE Function Do in Power BI?
LOOKUPVALUE function is similar to the VLOOKUP function in MS Excel, which looks for the required column from one table to the other based on the search value. Since we already know enough about VLOOKUP we will not go deep into this function theoretically, so let’s look at the scenario now.
I have three tables with me, below are the screenshots of the same.
We have three tables named “Product_Table, Tax-Table, and Discount_Table” respectively.
In Product_Table we don’t have “Tax %” and “Discount %” information, which is there in the other two tables. So in all the three tables’ common column is “Product” so using this we need to fetch the data to “Product_Table”.
Before we apply the LOOKUPVALUE function let’s look at the syntax of this function.
Result Column Name: This is nothing but from then other tables from which column we need the result. So for an example from “Tax_Table” we need results from the “Tax %” column and from “Discount_Table” we need results from the “Discount %” column.
Search Column Name: This is nothing but in the targeted table (Tax_Table or Discount_Table) based on which column we are looking for the Result Column. So our Search Value: This is the column name in the present table (Product_Table) which is the same as the column in the Search Colum Name of other tables.
So, eventually Search Column Name and Search Value both columns should be the same. Search Column Name is in from the result column table and the search value column will be from the present table where we are applying the LOOKUPVALUE function.
Example of LOOKUPVALUE DAX Function in Power BI
Above is the data we are using to apply the LOOKUPVALUE Dax Function in Power BI. You can download the workbook from the below link and can use it to practice with us.
Upload all the three tables to the Power BI file to start the demonstration.
- For “Product_Table” we need to fetch the values from the other two tables, so first we will fetch “Discount %” from “Discount_Table”. Right-click on the “Product_Table” and choose “New Column”.
- Give the name for the “New Column” as “Discount %”.
- Open LOOKUPVALUE function now.
- The first argument is Result Column Name since we are looking for the discount percentage from “Discount_Table” choose the “Discount %” column name from “Discount_Table”.
- The next argument is Search Column Name 1 so this will be the “Product” column name from “Discount_Table”.
- The next argument is Search Value so this will “Product” column name from “Product_Table”.
- Ok, we are done close the bracket and hit enter key to get the result.
There you go we have got the result from “Discount %” from “Discount_Table”. But when we look at the result column it is not in percentage format, so we need to change the number format to percentage format.
- Go to the “Modelling” tab, choose the “Format” as “Percentage” and keep the decimal place as 2.
- This will apply the format to the selected column as below.
- Similarly, now we need to insert one more column to fetch “Tax %” from “Tax_Table”, as usual, right-click and choose “New Column”, give the name to the new column as “Tax %” and open LOOKUPVALUE function again.
- This time Result Column Name will be from “Tax_Table” i.e. “Tax %”.
- Search Column Name will be the “Product” column name from “Tax_Table”.
- The next argument is Search Value, so this will the “Product” column name from “Product_Table”.
Close the bracket and hit enter to get the “Tax %” values.
Like this using the Power BI LOOKUPVALUE function, we can fetch data from one table to another.
Note: Power BI LOOKUPVALUE file can also be downloaded from the link below and the final output can be viewed.
Things to Remember
- LOOKUPVALUE is incorporated into Power BI as a lookup value function.
- If the lookup value is not found then it will return blank as the result.
- Result Column & Search Value columns are the same in both the tables.
- Unlike VLOOKUP we need to give any column name and range lookup parameters.
This has been a guide to Power BI LOOKUPVALUE. Here we discuss the examples of LOOKUPVALUE Dax Function in Power B to fetch data from one table to another. You may learn more about Power BI from the following articles –