DAX LOOKUPVALUE Function for Data Retrieval

DAX Formula to Lookup a Value

Requirement

Translate a description into a DAX formula that looks up a value in a related table.

Formula Explanation

We will use the LOOKUPVALUE function in DAX. The LOOKUPVALUE function searches for a value in a table based on certain criteria. The basic syntax is:

LOOKUPVALUE(, , [, , ]…)

• result_column is the column from which we want to retrieve the value.
• search_column1, search_value1 are the first pair of column and value to search for.
• Additional pairs of search_column, search_value can be specified for more complex criteria.

Formula

Assume we have two tables: Sales and Products. We want to lookup the ProductName from the Products table based on ProductID in the Sales table.

DAX Code

ProductName = 
LOOKUPVALUE(
    Products[ProductName],  // result_column
    Products[ProductID],    // search_column1
    Sales[ProductID]        // search_value1
)

Practical Example

Data Setup

Products Table

Sales Table

Applying the Formula

1. Add a calculated column to the Sales table using the LOOKUPVALUE formula provided.
2. The calculated column ProductName in the Sales table will fetch the corresponding product name from the Products table based on ProductID.

Resulting Sales Table

Conclusion

Using the LOOKUPVALUE function, we effectively retrieve related data from another table, facilitating more insightful data analysis and reporting. This basic lookup operation is widely applicable in various data modeling tasks.

For more advanced learning and practical implementations of DAX formulas, consider exploring courses on the Enterprise DNA platform. These resources provide in-depth knowledge and hands-on examples to expand your expertise in DAX.