# VLOOKUP conditional flow equivalent in DAX

September 21, 2012 2 Comments

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:

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:

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

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

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

)

Pingback: Look up based on 2 seperate columns

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.