OFFSET double lookup in PowerPivot
March 8, 2012 7 Comments
One of those really great functions in the native Excel environment is OFFSET. In combination with other functions like MATCH, it can generate a type of ‘double lookup’ that can be really helpful when creating dynamic reports. For example, the table below describes a matrix defining a coordinate of units for each combination of ‘Band’ and ‘Group’.
We want to use this table as a lookup reference when generating matches for the following values:
Take for example, the first row. We first look at the ‘Band’ value which is B in this case and find it in our lookup table. Then we go down the rows and, using an approximate match logic, find the group for which the value of 120 belongs to. That would be Group 3 (which has unit values going from 70 to 129).
In Excel, we can use a formula like the one below to generate this ‘double lookup’ calculation:
OFFSET($A$1,1,MATCH(B11,$A$1:$D$1)-1,4, 4),(4 – MATCH(B11,$A$1:$C$1)) + 1,TRUE)
This formula finds the appropriate columns first (column B) and then uses an approximate match VLOOKUP to find the row (#4) and column ( D ) to retrieve the right group. The final result is:
If we are analyzing data with PowerPivot, we want to be able to generate the same effect within the PowerPivot itself without having to drop all data into Excel and do the OFFSET lookup there. This could be because we are scanning millions of rows or simply because we want to keep all calculations in the same place (the PowerPivot database)
In order to do this we need to first understand one thing: we must unpivot the lookup table so we can successfully generate the lookup in DAX. Unlike what you can do with the OFFSET function – in which you can specify a column / row coordinate dynamically – in DAX we will be only filtering rows.
If our data is in a database like SQL Server, we can do this by executing the following T-SQL expression:
SELECT Band, [Group], Units FROM
(SELECT BandA, BandB, BandC, [Group] FROM Table1) t
(Units FOR Band IN (BandA, BandB, BandC)) as unpvt
ORDER BY Band, [Group], Units
The result will be the following:
In the case you are relying on data which resides only on your spreadsheet, you can use the ‘Multiple Consolidation Ranges’ wizard to generate the same effect. This wizard is found by going to File –> Options –> Customize Ribbon. On the ‘Choose Commands From’ dropdown, select ‘All Commands’ and then find and add to your ribbon the ‘Pivot Table and Pivot Chart Wizard’. Finally, click on the wizard button we just added to the ribbon. You should see the following window:
Notice that I moved the ‘Group’ column to the left. This is because the wizard expects the categories on the first column. Select ‘Multiple consolidation ranges’ and follow the instructions. Once complete, you will get a pivot table using the data from our lookup table. Move the ‘Row’ and ‘Column’ fields to the row labels. Finally, flatten the table out (using the PivotTable Options context menu) and you will end up with the following result:
Now you are ready to import the unpivoted lookup data into the PowerPivot window. Be sure to change the column names to something more meaningful than ‘Column’, ‘Row’ and ‘Total’. In my case, I have called them ‘Band’, Group’ and ‘Units’.
Finally, import the items that we are going to be using against the lookup (2nd screenshot on this blog entry) – I called it ‘Data’. Notice that this is yet another scenario in which there is no actual relationships between the tables in the data model. All the work will be done using a DAX expression:
DoubleLookup:=IF ( HASONEVALUE( Data[Band] ) ,
LASTNONBLANK( Lookup[Group], 1) ,
Lookup[Band] = VALUES(Data[Band] ) &&
Lookup[Units] < VALUES( Data[Units] )
Notice the expression is very readable and shows how a fairly simple DAX formula can have a powerful dynamic effect. LASTNONBLANK selects the last [Group] value on a filter context for the current Band where the lookup units are less than the current unit value. The output matches the more complex expression we defined using OFFSET, MATCH and VLOOKUP:
The formula works even if we have repeated ‘Band’ values. In that case, I suggest adding an identity column to the table in order to break down the resulting value accordingly –
This post has been featured on Excel’s MVP Bill Jelen “VLOOKUP Week” (March 25 – 31, 2012 ) which groups podcasts and blogs describing the use of this and other interesting Excel-related lookup functions and techniques. For more information, visit Microsoft’s Excel team blog @ http://tinyurl.com/c2aw89y , or directly access VLOOKUP’s week website @ http://vlookupweek.wordpress.com/