Help us promote SQL Saturday 174 and get free training in return!

If you intend to attend the SQL Saturday ‘BI’ Edition in Charlotte, NC on October 27, you may be the lucky winner of an *Annual Plus* subscription from PluralSight, a leading developer training company (valued at $499).

How?  Help us promote the event by sending an email to your friends and colleagues encouraging to attend.  Please be sure to cc SQLSaturday174@SQLSaturday.com so we know you are spreading the word (Be sure to direct your contacts to register on our event website @ http://www.sqlsaturday.com/174/eventhome.aspx )

Send the email between now and midnight on Sept. 30th and you will be entered in a drawing to win the free training.  We’ll do the drawing at the SQL Saturday closing remarks.

[your name can only be entered once and you must be present to win]

Thank you and I hope to see you there! 🙂

image

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