By Dany Hoter
We always think about a data model with multiple tables and relationships between them as a big improvement over the common practice of combining tables using VLOOKUP expressions.
(Watch Video for more: PowerPivot Relationships are EASIER than VLOOKUP, not just faster)
I came across a customer request that forced me to admit that in some aspects a VLOOKUP is more flexible than relationships. To solve some specific scenarios we have to use modeling methods which are not elegant and not efficient.
VLOOKUP better than Relationships?
I hope that some of the readers will know to defend the cause of the data model and find a magic solution that is both elegant and efficient.
How can good old VLOOKUP be better?
A solution based on VLOOKUP is using a separate formula for every row, this is what makes it cumbersome, slow and error prone – right?
Yes but this is also what makes it more flexible in some cases.
Let’s describe some reasons why is it more flexible and give some examples:
VLOOKUP can use a different way to find the right row based on the input value
, VLOOKUP (A6,lookuptable6,3,false)
, VLOOKUP (A5,lookuptable5,4,false))
If lookup value is empty in our table, there is no point in looking for it in the lookup table and instead a different lookup table is used and a different lookup value is searched for.
For example (download), if a state column is empty or contains “NA”, return a value from the countries table using the country as a search value.
VLOOKUP gives us the flexibility to lookup State or Country names
How would you solve this in a Power Pivot data model? Create a calculated column in one table that contains the state or the country and create a relationship with a table that contains both countries and states. Or let the relationship fail to find a matching row in the lookup table and apply the solution from the next paragraph.
The expression can make a decision on a row by row basis how to behave in cases the VLOOKUP doesn’t find a matching row.
I can decide what to show for every column that is based on VLOOKUP in case it is not found.
In the first example I show a constant value.
In the second example I show the key itself
In the third case I perform another VLOOKUP if the first one fails.
What is the equivalent behavior of a relationship?
Any value that is coming from the 1 side of a relationship and is not found in the 1 side returns as blank().
(blank) values when using Lookup Tables
can cramp your style
This is not always friendly to the consumer of a report.
It may be expected that not all values in the many side are not found in the 1 side but blank is not always the best option to show instead.
What can we do to solve the problem
Exactly what we are trained not to do – create a calculated column in the many side.
Assume you have a column in the products table called [Product Name]. The table is on the 1 side of a relationship with the sales table.
You can create a column called [Product Name] in the sales table with the DAX expression
Now we should hide the [Product Name] column from the products table and use the one in the sales table.
Elegant? – no
Efficient? – no
But this is the best solution I can find.
In the included Excel file I used both the VLOOKUP solution and the DAX solution in the same table
So I wouldn’t be seen stupid I ran this problem through one of the leading experts and he agreed that it is a real scenario so now I propose this as a challenge to both the experts and the team who owns the modeling experience.