Simulating an "approximate match" VLOOKUP in PowerPivot

It is often said that relationships in PowerPivot work in a similar way to VLOOKUPs.  In reality, this is only partially true. If we examine the claim a bit closer we realize that Excel’s VLOOKUP function has a parameter in which it is possible to use an approximation when matching values against the lookup table:

image

Regular PowerPivot relationships emulate the ‘exact match’ option of the Excel VLOOKUP function And although PowerPivot relationships are a lot more powerful, being able to retrieve lookup values over huge tables at amazing speed, we are still left with the question: how can we achieve same type of behavior used by the ‘approximate matches’ option of the VLOOKUP function? Lets walk through the process.

Imagine we have the following tables:

image

As you can see, the table at the left is the one that will be executing the lookup call against that table on the right.   The first thing we need to ensure is that there are no relationships among those tables in the PowerPivot model.   That’s right, we will be generating the entire lookup as a DAX calculation.  In other words, we will be generating a calculated relationship.

Lets first examine what Excel yields when using the VLOOKUP function with approximate matches:

=VLOOKUP([@TableA],Table2[TableB],1,TRUE)

image

If an exact match is not found, VLOOKUP returns the next largest value that is less than the lookup value.

In PowerPivot, we would generate the same effect with this calculation:

If (
Hasonevalue ( TableA ),
Calculate (
Max ( TableB[TableB] ),
Filter ( TableB, TableB[TableB] <= Values ( TableA[TableA] ) )
)
)

The first condition (HASONEVALUE) forces the computation to be executed only when there is one and only one value on TableA (in other words, it avoids calculating for the grand total row if there was one as this would be meaningless to do so).

The actual computation is done via the CALCULATE expression.  As you can see, it takes the maximum value of TableB in a context modified by the second parameter.  As there is no relationship between TableA and TableB, context is not propagated and the calculation uses all rows in TableB.   The FILTER function then filters the table and returns only those rows which are less than or equal to the current value in TableA.

The result is this:

image

As you can see, we were able to match the output we got with VLOOKUP.   But what will happen if we use are trying to match text values instead of numbers?

image

We get the following error:

Calculation error in measure ‘TableA'[Measure 3]: The function MAX takes an argument that evaluates to numbers or dates and cannot work with values of type String.

A solution is found by using the LASTNONBLANK function:

If (
Hasonevalue( TableA_Text ),
Calculate (
LASTNONBLANK ( TableB_Text[TableB], 1 ),
Filter ( TableB_Text, TableB_Text[TableB] <= Values ( TableA_Text[TableA] ) )
)
)

image

LASTNONBLANK requires two parameters, and we can use the first one for the column for which we want to get the last value in the lookup table.  The second parameter must be an expression that evaluates for blanks;  by using ‘1’  here we retrieve all rows on that table but that is OK as the table is being filtered by the second parameter of the CALCULATE function.  In other words we truly get the last value in context which is exactly what we need.

This last calculation can be used with text values, but it will also work with numbers or dates, and it is a great pattern when simulating VLOOKUPs with approximate matches.

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/

Advertisements

8 Responses to Simulating an "approximate match" VLOOKUP in PowerPivot

  1. Pingback: Javier Guillén “Simulates an “approximate match” VLOOKUP in PowerPivot” « VLOOKUP WEEK

  2. What if have multiple criteria that I need to match to with one approximate match. Any idea on how you would change the dax formula to match.

  3. javierguillen says:

    Can you post an example of what the multiple criteria would look like?

  4. Not sure how I can attach a sample workbook but I can try to explain. So if I ran a commercial on 1/1/2012 that ran on station ABC with a media code of XYZ and I want to attribute any orders to that commercial that come in with station code ABC and media code XYX but I had an order on 1/1/2012 and one on 1/3/2012 I would want to attribute all orders to that airing in my data set but because the the second airing doesn’t have an exact match on the date field I only get one out of two orders that should be attributed. I was hoping that I could have some parameters that match exactly and a parameter where there is an approximate match on the date. We have this type of situation all the time and I think it is is a fairly common business problem that has not been addressed in PP. Maybe it has but I just haven’t seen the solution. Hope you can help. I can send a more robust example by email if you would like.

    Kind Regards,

    John Bradley

  5. Mike says:

    Javier,

    If you are able to answer John’s question, can you post it on your blog? I am looking for the same thing.

    Thanks,
    Mike

  6. javierguillen says:

    Sure Mike, I will definitely post it. John, have you sent the sample? I don’t believe I got it yet…

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

%d bloggers like this: