OFFSET double lookup in PowerPivot

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’.

image

We want to use this table as a lookup reference when generating matches for the following values:

image

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:

VLOOKUP(A11,

OFFSET($A$1,1,MATCH(B11,$A$1:$D$1)-1,4, 4),(4 – MATCH(B11,$A$1:$C$1)) + 1,TRUE)

image

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:

image

Pretty powerful.

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

UNPIVOT

    (Units FOR Band IN (BandA, BandB, BandC)) as unpvt

ORDER BY Band, [Group], Units

The result will be the following:

image

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:

image

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:

image

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] )  ,

CALCULATE(

        LASTNONBLANK( Lookup[Group], 1) ,                   

                       FILTER( Lookup,

                        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:

image

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 -

image

  Output:

image

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/

About these ads

7 Responses to OFFSET double lookup in PowerPivot

  1. Pingback: “VLOOKUP, OFFSET and MATCH in PowerPivot” from Javier Guillén « VLOOKUP WEEK

  2. Mohan NC says:

    Hi, I am a novice to Powerpivot. I am looking for the equivalents of Index/Match functions in Powerpivot. I have list of several hundred employees with their salary placement (Lane and Step). The value based on Lane and Step should be picked from a salary matrix (The top row consists of Lane fields AA, BA, MA, Phd and the 1st column consists of Steps 1 to 35). So, for example , if an employee is BA/Step-5, I should have the “Salary” field pick up the value from the matrix. how do I do this in Powerpivot? I greatly appreciate your help.

  3. Mohan NC says:

    Thank you javier for your help.
    Here is the sample employee data:

    Emp Name Lane Step Salary
    Sarah BA 1
    Michael AA 5
    Robert PHD 8
    Richard MA 4

    Here is the sample matrix
    STEP AA BA MA PHD
    0 10,000 10,350 10,712 11,087
    1 10,325 10,686 11,060 11,448
    2 10,661 11,034 11,420 11,820
    3 11,007 11,392 11,791 12,204
    4 11,365 11,763 12,174 12,600
    5 11,734 12,145 12,570 13,010
    6 12,115 12,540 12,978 13,433
    7 12,509 12,947 13,400 13,869
    8 12,916 13,368 13,836 14,320
    9 13,336 13,802 14,285 14,785

    • javierguillen says:

      In a similar way to the one described on this blog post, you can use the ‘Multiple Consolidation Ranges’ Wizard in Excel to ‘unpivot’ your lookup table. This is because PowerPivot lookup’s are based on rows primarly, not columns. The result you get is the following:

      Lane Step Salary
      AA 0 10000
      AA 1 10325
      AA 2 10661
      AA 3 11007
      AA 4 11365
      AA 5 11734
      AA 6 12115
      AA 7 12509
      AA 8 12916
      AA 9 13336
      BA 0 10350
      BA 1 10686
      BA 2 11034
      BA 3 11392
      BA 4 11763
      BA 5 12145
      BA 6 12540
      BA 7 12947
      BA 8 13368
      BA 9 13802
      MA 0 10712
      MA 1 11060
      MA 2 11420
      MA 3 11791
      MA 4 12174
      MA 5 12570
      MA 6 12978
      MA 7 13400
      MA 8 13836
      MA 9 14285
      PHD 0 11087
      PHD 1 11448
      PHD 2 11820
      PHD 3 12204
      PHD 4 12600
      PHD 5 13010
      PHD 6 13433
      PHD 7 13869
      PHD 8 14320
      PHD 9 14785

      I called this ‘SalaryLookup’ on the PowerPivot model. Then I imported your Employee table as well. With that, a DAX calculated column will take care of finding the right value:

      Calculate (
      Values ( SalaryLookup[Salary] ),
      Filter ( SalaryLookup, SalaryLookup[Lane] = Employee[Lane] && SalaryLookup[Step] = Employee[Step] )
      )

      The result is correct based on a cross-reference of Lane and Step:

      EmpName Lane Step Salary
      Sarah BA 1 10,686
      Michael AA 5 11,734
      Robert PHD 8 14,320
      Richard MA 4 12,174

      Hope that helps

  4. Mohan NC says:

    Thank you so much Javier. Your prompt help is greatly appreciated.

  5. javierguillen says:

    Sure! Doing the equivalent of ‘VLOOKUPs with multiple matching criteria’ is easy in PowerPivot; the thing to keep in mind is the way data must be structured. Vertical Lookups are always preferred, rather than a matrix that relies on horizontal lookups as well. In a way, this ‘limitation’ also keeps data more tidy, and easier to manage.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 60 other followers

%d bloggers like this: