Mastering VLOOKUP in Power BI

Power BI and VLOOKUP: Replicating with DAX Functions

To start off, VLOOKUP is a function used in Excel that looks for an entry you want to find, existing somewhere within the first column of range. In Power BI there is no direct vlookup of Excel. On the other hand, Power BI uses DAX(Data Analysis Expressions) to provide similar capability. Here is how you can replicate VLOOKUP in Power BI:

Suppose you have two tables:

• Employee Data: Contains columns ‘EmployeeID’, ‘EmployeeName’, ‘DepartmentID’, and ‘Salary’.
• Department Info: Contains columns ‘DepartmentID’, ‘DepartmentName’, and ‘Location’.

You want to look up the “DepartmentName” in the “Department Info” table based on the ‘DepartmentID’ in the “Employee Data” table.

Step-by-Step Guide

1. Import Data:
   Load your data into Power BI. You can do it by clicking on Home > Get Data >> Select Source (Excel, SQL Server etc.)
2. Establish a Relationship:

   Power BI works on the relational model. You should create a relationship between the tables instead of using VLOOKUP.

   Go to ‘Model’ view.

   Take the ‘DepartmentID’ in “Employee Data” table and drag it to join with Department ID of same name on right side. This makes a link between the tables with respect to ‘DepartmentID’.

3. Use Related Function:

   Now that a relationship is established, you can use RELATED function in DAX which is similar to doing VLOOKUP.

   As an example, if you wanted to extract the ‘DepartmentName’ from the table “Employee Data” it might look like this;

   Navigate to the data view or report viewer.

   Navigate to the “Employee Data” table

   Click on Modeling, then New Column to create a new column.

   Enter the DAX formula:

   DepartmentName = RELATED('Department Info'[DepartmentName])

   This will create a new column in the “Employee Data” table with ‘DepartmentName’ of corresponding ‘DepartmentID’.

4. Using LOOKUPVALUE:

   Or if you do not want to create a relationship between tables, then LOOKUPVALUE function can be use which is similar to vlookup in excel.

   Creating a column to get ‘DepartmentName’

   Then add a new blank column to your “Employee Data” table.

   DepartmentName = LOOKUPVALUE('Department Info'[DepartmentName], 'Department Info'[DepartmentID], 'Employee Data'[DepartmentID])

   Where the ‘DepartmentID’ matches, this formula retrieves the ‘DepartmentName’ from the “Department Info” table.

5. Use in Calculations or Reports:

   That will allow you to treat the ‘DepartmentName’ column like any other column in Power BI for calculations, visualizations and reports.

Key Points to Remember

• All about relationships: Power BI works best when you have a relational data model. Most rows should be related rather than looked up.
• Between RELATED and LOOKUPVALUE: use the first when there is already a relationship, the second for doing vlookups of course without an existing link.
• Powerful function — DAX Flexibility: Due to the feature of Column Binding and Relationship, you could enhance your simple VLOOKUP concept into a new level with other fancy functions; e.g., RELATED(), LOOKUPVALUE(), CALCULATE(), etc.

This is one way to have the behavior of a VLOOKUP but still Power Bi shines in relationships and DAX with more powerful data analysis capabilities.

Learn More About DAX Functions in Power BI

For more details on DAX functions, visit the Microsoft DAX Function Reference.