Introduction

Understanding the LOOKUPVALUE function in DAX is essential for anyone working with Power BI. In this tutorial, we will explore the LOOKUPVALUE function in depth, including its syntax, use cases, and a practical example. Whether you are a beginner looking to enhance your DAX skills or an advanced user preparing for Microsoft Exam PL-300 (Power BI Data Analyst), mastering the LOOKUPVALUE function will help you retrieve data efficiently and build insightful reports.

What is the LOOKUPVALUE function?

The LOOKUPVALUE function in DAX returns a single value from a specified column based on one or more search conditions. Unlike the RELATED function, which relies on existing relationships between tables,LOOKUPVALUE allows you to fetch data even when no direct relationship exists. This makes it a powerful function for retrieving values dynamically within your DAX calculations.

Syntax

The syntax of the LOOKUPVALUE function is as follows:

LOOKUPVALUE(
<result_column>, 
<search_column>, 
<search_value>
[, <search_column2>, 
<search_value2>, 
...]
[, <alternate_result>]
)

Let's look at the components in detail:

Component Description
<result_column> The column from which you want to retrieve the value.
<search_column> The column where you want to perform the search.
<search_value> The value you want to search for in the search column.
<search_column2> (Optional) Additional search column for multiple conditions.
<search_value2> (Optional) Additional search value for multiple conditions.
<alternate_result> (Optional) The value to return if no match is found.

Practical Example

Now, let's perform an example calculation with the LOOKUPVALUE function.

Data

We have already loaded data from an Excel file into Power BI Desktop. There are the following two tables:

exam_results_data_science: Contains exam results for Data Science courses.

student: Contains student information.

As you can see, in this example there is no connection between the tables.

Example 1: LOOKUPVALUE function with one condition

Now, we want to include the name of the students in the table exam_results_data_science. The names need to be retrieved from the table student. In order to do this, we use the LOOKUPVALUE function with one condition. If no match was found in the table student, then an alternate value "Unknown" should be returned.

Create a new calculated column student_name and enter the following DAX code:

You can view this post with the tier: Academy Membership

Join academy now to read the post and get access to the full library of premium posts for academy members only.

Join Academy Already have an account? Sign In