VLOOKUP conditional flow equivalent in DAX

Recently I was browsing an Excel forum and someone asked an interesting question.  How can one write a formula in which a value must be found in a lookup table, and if none is found then instruct the expression to retrieve a value from another lookup table?

In Excel, this can be done using a formula like this one:

=IFERROR(VLOOKUP(A1,TableA,2,FALSE),VLOOKUP(A1,TableB,2,FALSE))

Why would we want to use this kind operation? If, for example, we are consolidating a table of account balances.  Given the existence of multiple ‘account types’, not all account might be stored in the same table.  For example:

image

The behavior in Excel relies on the fact that an unmatched VLOOKUP condition will return an error (#NUM).  In DAX, however, an unmatched RELATED() function simply returns an empty value.  As such, an equivalent DAX expression would not yield the appropriate values.

This is the Tabular data model we are working with:

image

The equivalent DAX calculated column would yield the following (in the ‘Total List of Accounts’ table):

=IFERROR(

  RELATED(‘Credit Cards'[balance]), RELATED(‘Checking Accounts'[balance]) )

image

In other words, only on relation gets resolved.  Armed with the knowledge that RELATED() returns empty values for unmatched elements, we are tempted to use ISBLANK() in the expression:

=IF(
ISBLANK(RELATED(‘Credit Cards'[balance])),
RELATED(‘Checking Accounts'[balance]),
RELATED(‘Credit Cards'[balance])
)

However, this yields the same result as the one one shown above.  In my opinion, this is a bug and have opened a connect item about it (you can vote for it here ).

So how can this be solved?    As a calculated column, we can simply concatenate the output of both RELATED() calls to get the result we need:

=RELATED(‘Credit Cards'[balance]) + RELATED( ‘Checking Accounts'[balance] )

image

if you suspect you may have an identifier that is present in both tables, it will be necessary to understand what business rule should be used to prefer one over the other one.  If, as an arbitrary example, we wanted to prefer credit cards over checking accounts, the expression would be:

=IF(
CALCULATE( COUNTROWS(‘Credit Cards’ ) ),
RELATED( ‘Credit Cards'[balance] ),
RELATED( ‘Checking Accounts'[balance] )
)

If a measure was need instead – perhaps because you need the balance to be used over an extended expression leveraging the query context – , the formula would need only to use a function that generates row context like SUMX:

=SUMX(
‘Total List of Accounts’,
IF(CALCULATE( COUNTROWS(‘Credit Cards’ ) ),
RELATED( ‘Credit Cards'[balance] ),
RELATED( ‘Checking Accounts'[balance] ) )
)

image

Advertisements

2 Responses to VLOOKUP conditional flow equivalent in DAX

  1. Pingback: Look up based on 2 seperate columns

  2. What about using
    IF(ISBLANK(LOOKUP( using table 1) ), LOOKUP(using table 2), LOOKUP( Using table1)

    That would the actual equivalent but! your solution would be the best practice as RELATED takes less to calculate.

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: