Equivalent of VLOOKUP in DAX–Part I


One frequent DAX requirement is to write a formula that behaves like the VLOOKUP function in Excel.

Gehrard Brueckl recently blogged about how to map a date to a fiscal period, when fiscal periods are defined in a separate table, and each period is defined by its start date. Ken Puls recently wrote an article about calculating an effective tax rate with DAX.

In this first post, I will present the LOOKUPVALUE function.

Quick notes about VLOOKUP

VLOOKUP, a classic Excel function, accepts two values (TRUE or FALSE) for its last argument.

Assuming we defined names for Value, Range, i, the formula will look like this:
=VLOOKUP(Value, Range, i, FALSE)

VLOOKUP will look for the first occurrence of Value in the first column of Range. If the value cannot be found, the formula will return #NA, else it will return the value in the i-th column on the same row.

Note that VLOOKUP also works with arrays.

When the last parameter is TRUE (or omitted), then VLOOKUP requires your data to be sorted in ascending order. It will look for the last value in the first column of Range that is not greater than Value, and will return the value in the i-th column on the same row, or #NA if none exists.

More details on VLOOKUP here, there, or everywhere on the internet.

LOOKUPVALUE

Syntax

DAX V2 introduced a LOOKUPVALUE, that has a related behavior.

A notable difference is that LOOKUPVALUE allows you to provide criteria on several columns. In Excel formulas, this would require you to use concatenate columns, or even replace VLOOKUP with some INDEX/MATCH formula.

The syntax looks like this:
= LOOKUPVALUE( Table[OutputColumn], Table[LookupColumn1], “SearchValue1”,  Table[LookupColumn1],  “SearchValue2”)

The syntax looks like the vector form of the LOOKUP function, if you put the order of arguments aside. It actually looks like the syntax of the LOOKUPS function, which does not actually exist. Smile

Basically, you first have to define which column contains your output, then define your first lookup column, then provide the value you are looking for, then define your second lookup column, and so on …

Behavior

LOOKUPVALUE behaves differently from VLOOKUP:

  1. whereas VLOOKUP works with arrays, LOOKUPVALUE does not work with column expressions. In other words, the columns arguments must reference columns that physically exist in your model. Also, remember that each column must belong to the same table.
  2. if several rows in your table match your criteria, then VLOOKUP in Excel will only return the first (or last) match. On the contrary, LOOKUPVALUE in DAX may return an error if several rows match your criteria. This will occur if [OutputColumn] does not contain the same value for all matching rows.
  3. a BLANK value is returned if there is no match.

Also, not surprisingly, you must address columns by names and cannot use indexes to do this.

When to use LOOKUP value?

When your lookup table is a parameter of your model. By that, I mean a static table that you can import in your model.

Obviously, when the behavior described above suits your requirements.

When you want to calculate search values on the fly, or use different search values that come from different tables. In other words, when you cannot use PowerPivot relationships.

About these ads

8 Responses to “Equivalent of VLOOKUP in DAX–Part I”

  1. fbrossard Says:

    Hi Laurent,
    Great post.
    For my part, I often use LOOPVALUE in two main uses cases :
    1. to implement parent-child hierachies with calculated columns
    2. to define filter on the fly for my measures (very useful for time calculation such as week to date, year-over-year calculations, etc …)
    Fred.

  2. TheDataSpecialist Says:

    Hi Fred,

    thanks for the comment.
    I would love to hear/read more about point 1.

    Laurent.

    • fbrossard Says:

      Hi Laurent,

      To implement Parent/Child hierachies, the first step is to create a calculated column to retrieve for each rows the entire path such as :
      ‘TableName’[HierarchyPath] = PATH([NodeId], [ParentNodeID])

      And then you have to create a calculated column for each level of your hierachy using the LOOKUPVALUE such as :
      ‘TableName’[Level1]= LOOKUPVALUE ([Node value to show], [NodeId], PATHITEM ([HierarchyPath], 1))

      ‘TableName’[LevelN]= LOOKUPVALUE ([Node value to show], [NodeId], PATHITEM ([HierarchyPath], N))

      Here are two references on the subject :
      . Kasper de Jonge : http://www.powerpivotblog.nl/powerpivot-denali-parent-child-using-dax
      . Alberto Ferrari : http://sqlblog.com/blogs/alberto_ferrari/archive/2011/07/19/parent-child-hierarchies-in-tabular-with-denali.aspx

      See U.

      Fred.

  3. Equivalent of VLOOKUP in DAX – Part II – using TOPN | The Data Specialist Says:

    [...] Equivalent of VLOOKUP in DAX–Part I [...]

  4. DataMonkey Says:

    Is there a way to use Lookup or some other function in DAX to do a “like” or a % match? For instance if the base table has a long string and the lookup table has the substring that I want to match, is there a way to use Lookup?

    • TheDataSpecialist Says:

      In I present a pattern mimicking VLOOKUP’s behaviour. The functions like FIND / SEARCH and LEFT / RIGHT / MID allow you to check whether a string contains another one.

      • Clarity (@Clarity14794448) Says:

        I’m not sure I got the link from “In I present a pattern…”. Was there a link that was missed out there? Also, I understand that FIND/SEARCH and the other functions mentioned help to look for a single string in another string. But in this case I need LOOKUP’s iterative power to do that FIND on each of the lookup strings and return the first match. That isn’t clear from this post or your comment. Any ideas on how I could achieve this with LOOKUP or any other DAX function?

      • TheDataSpecialist Says:

        Indeed, my previous reply contained a link to part II of this “series”. As far as I know LOOKUPVALUE does not accept wildcards.

        Here is a quick summary of the approach I describe in part II:
        - iterate over a table with the FILTER function (using the appropriate text search function) to determine which rows match your pattern.
        - use TOPN to make sure the expression only returns one row
        - use this row as context argument of a CALCULATE expression – get the value you want with the VALUES function:
        CALCULATE( VALUES( ResultColumn] ), TheRowExpression )
        where TheRowExpression is a TOPN(FILTER( … )) expression.


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 25 other followers

%d bloggers like this: