The more I work in Power Pivot, the more I’m amazed at what can be done using DAX in Excel, Microsoft’s powerful expression language for creating custom calculations, aggregations, and references within and across data entities. In this post, I take a look at the very useful LOOKUP VALUE expression, for when you want to scan any table in your data model, find a specific value, and relate that value to the current row.
Scenarios for using LOOKUPVALUE expression in DAX
One case where LOOKUPVALUE came in handy for me: I wanted to count the activities associated with leads that converted to a Won opportunity and compare the activity count to Lost opportunities. I could not create a direct relationship between the leads and opportunity records because both had duplicate IDs (in Power Pivot, at least one data set must contain unique values in order to create a join).
The LOOKUPVALUE expression used in a calculated column enables you to specify the exact conditions for returning a data value across tables or within the same table without the tables being explicitly related via Create Relationship functionality of Power Pivot. Duplicates can be overcome when you specify criteria where unique records can be found in a subset of the table rows.
Here is my sample data set for activities associated with sales leads: Company Name, the Topic associated with the activity, Start Date, and the Activity Type (phone or email). There could be more than one activity per company and topic.
Lead Activities samples data set
My opportunity data also stored multiple rows for each opportunity, which was identified by Account company name and Topic. This table also tracked changes in the Opportunity Stage values along with status.
Opportunities sample data set
Create a Common Key for Lookup Across Tables
Before performing the look up on the Opportunity data, I created a calculated column for a shared key by concatenating Company Name (Account) and Topic in each table, using the ampersand & operator. The syntax to perform a concatenation in DAX is:
= [column name] & [column name]
In my case, the ID was concatenated as = [Company Name] & [Topic] for the leads table and = [Account] & [Topic] for the Opportunity table.
Create the LOOKUPVALUE expression
Now to look up the activities in my Leads table were associated with opportunities closed as a Win. In English, this translates as “look for the row in the Opportunity table that has the same Account and Topic names in my Lead Activities table (based on the key I created) and having the Opportunity status value of “Won”.
The syntax for LOOKUPVALUE is as follows:
= LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value> , <search_columnName>, <search_value>…)
So in DAX, my query translates as:
= lookupvalue (Opportunities[Status], Opportunities[ID], LeadActivities[ID], Opportunities[Status], “Won”)
In effect, DAX is looking in the Status column for the value of “Won” where the ID for the row in the Opportunities table matches the ID on the row in the Leads Activities table. Since my expression specifies the lookup only where the status = “won”, and there is only one row for each Account + Topic with that status value, we no longer have the problem of duplicate rows.
There is always a work-around in DAX
The LOOKUPVALUE expression is one reminder of the many ways you can use DAX to work around what at first seem like limitations in Power Pivot. Speaking of creative work-arounds, just want to take a moment to support all the great examples of DAX that Kasper de Jonge makes available on his blog! His work is always an inspiration.