PowerPivot VLOOKUP equivalent

The RELATED function makes it easy to pull data from one table to another, but what about looking up a value within a table?  I came across this issue recently when a client wanted to compare two rows of data that was reported between consecutive months.  After some search engine queries and some trial and error, I found that VLOOKUP could be accomplished with four DAX functions, CALCULATE, DISTINCT, FILTER, and EARLIER.  The resulting formula looked like this:

=CALCULATE(DISTINCT(‘Table1′[Value]),FILTER(‘Table1’,’Table1′[CalculatedID]=EARLIER(‘Table1′[LookupID])))

The following is a breakdown of that formula into 4 parts:

1)     (‘Table1’[LookupID])

1. Within Table1 I created another calculated column called LookupID.  LookupID represented another row’s CalculatedID.  In my case the CalculatedID followed the format YYYY-MM-### where the first four characters were the year the data was reported, the second two characters were the numerical month the data was reported, and the last three characters represented a unique identifier for the group the data was reported under (called GroupID).  To generate the LookupID column I subtracted the month and year the data was reported by one month using the DATEADD formula (example below).

FORMAT(DATEADD(‘Table'[MonthYear],-1,MONTH),”yyyy-mm-“))

Then I appended the GroupID field using the CONCATENATE function (example below).

CONCATENATE(FORMAT(DATEADD(‘Table1′[MonthYear],-1,MONTH),”yyyy-mm-“)),’Table1’ [GroupID])

 LookupID now represented the CalculatedID of another row, and this other row contained the previous month’s data.

2)     ‘Table1′[CalculatedID]=EARLIER(‘Table1′[LookupID])

1. The EARLIER function conducts a table scan of Table1 and finds instances where LookupID is equal to the CalculatedID for the current row.

3)     FILTER(‘Table1’,’Table1′[CalculatedID]=EARLIER(‘Table1′[LookupID])))

1. In this equation Table1 is filtered by the statement described in part 2.  Therefore only the rows meeting this statement are returned.

4)     CALCULATE(DISTINCT(‘Table1′[Value])…

1. This part of the equation returns the distinct values for the column “Value” found in the result set returned by the FILTER function described in part 3.

This formula worked well for our client and we were able to compare groups of data between two months and build some nice reports on their SharePoint site.

Excel 2013 and SQL Server 2012

With PowerPivot for Excel 2013 and for SharePoint 2010 environments using SQL Server 2012, you can avoid this lengthy formula by using the new LOOKUPVALUEfunction in DAX.