- Ciao friends!
This video is an introduction to LOOKUPVALUE.
LOOKUPVALUE is quite a common function.
Its purpose is that of searching in a table for one value
and returning it.
How does it work?
Well, you need to provide LOOKUPVALUE
first the column you want to retrieve,
and then a set of conditions that need to be satisfied
in order to retrieve the column.
Let's see an example.
In the sales table,
I have a calculated column product unit price,
and you see that I'm using LOOKUPVALUE.
What I'm saying is that I want to retrieve the unit price
from the product table where the product product key
is identical to the current value of sales product key.
So the engine will open the product table,
filter out all the rows where product key
is not identical to sales product key
and then retrieve the unit price.
It's very simple in its usage, and it's actually
quite powerful because you can specify multiple conditions.
So let's see a few more examples of usage of LOOKUPVALUE.
The first one is retrieval of the exchange rate.
Now in my data model, I have a table that contains
the daily exchange rates.
And let's see the content of the table.
The daily exchange rate, I'm not interested in the code,
I want to see the content has a date, a currency key
and the exchange rate.
So in order to retrieve an exchange rate,
I need a currency key and a date
and that will give me the exchange rate to US dollar
of that date.
So how did I do my calculated column to retrieve
the exchange rate of one specific sale?
First, I need the currency key.
If I want the exchange rate to Euro,
I use LOOKUPVALUE to find in the currency table
the value of the currency key where the code is Euro.
So I go from the string Euro to the currency key,
which is an integer that I will use
to search in my exchange rate table.
And then I retrieve the current date
from the current row context.
And finally, I use again LOOKUPVALUE to search
in the daily exchange rate
for the rate where the currency key is identical
to the currency key I computed earlier
and the date is the current date.
This will find the exchange rate on one date and return it.
You see that I'm using variables
in order to store temporary calculations.
This speeds up calculation
and makes the code much more readable.
LOOKUPVALUE is actually more powerful than that.
Indeed, whenever you use LOOKUPVALUE, you can use
not only columns of the tables you're searching into,
you can use the entire expanded table.
You don't know what an expanded table is?
Well if that is the case look up that link,
there, you will find a description
of what expanded tables are:
an important topic for any DAX developer.
Anyway, the expanded table means that I can use
any column of that table, plus any column of related tables.
So if you look at this code,
I need to first retrieve the currency key
from the currency table searching for Euro.
And then I use this value here in the daily exchange rate.
But what happens is that the daily exchange rate
has a relationship with currency.
So the expanded version of daily exchange rates
includes currency.
Therefore, I can use a shorter version of the same code,
where I use LOOKUPVALUE and look what I'm doing.
I'm retrieving the daily exchange rate and I am applying
a filter on the currency currency code for euro.
I don't need to grab the currency key,
that happens automatically because of expanded table.
So my two conditions are
one on the same table I'm searching into,
and one on a related table.
The code is simpler, and it's actually even a bit faster.
With that said, keep in mind that LOOKUPVALUE is powerful,
but a lot of people use LOOKUPVALUE
because they are used to VLOOKUP in Excel.
DAX is different.
DAX does not require you to use VLOOKUP to grab values.
Relationships are much better.
So whenever you work on a model,
if you find that you are using a lot of LOOKUPVALUE,
then it's likely you need to review your model
and create a few more relationships.
Nevertheless, LOOKUPVALUE prove extremely useful
whenever you want to search in the same table.
For example, what if I want to compute
the variation of price in the sales table.
I sell products with different prices,
and I want to understand the difference between
the previous price and the current price.
To do that, I can rank my orders one, two,
three, four, five by product.
And then I compare the current price
with the price of the same order in the previous ranking.
What I need to do is search
in the same table for a previous row.
That cannot be accomplished through a relationship
because I cannot create self-joins in DAX.
But LOOKUPVALUE proves useful.
Let me show you the code of the price variation,
which is just using LOOKUPVALUE.
It looks complex, but just because it's long, nothing more.
See what I'm doing, I first grabbed the previous rank order
which is the current rank order minus one.
I take the product key and the current net price
all this is stored in variables.
And then I use LOOKUPVALUE searching for the net price
where the rank order is the previous rank order,
so not this one, but the previous one,
and the product key is the same as the current product key.
This LOOKUPVALUE will return
the price of the previous order if any.
In case there is no order LOOKUPVALUE will return a blank.
And in that case, the COALESCE function will say,
well in case the result is blank,
I will return you the sales net price
so that the variation will be zero.
The last step of computing the price variation
is just a simple subtraction of one value from another one.
In such a case, LOOKUPVALUE proves invaluable
because there is no other way to retrieve these
previous price in an efficient way.
Speaking about efficiency,
keep in mind LOOKUPVALUE might slow down your code.
If you find out that you need to use a lot of LOOKUPVALUEs,
again, it's worth spending a bit more time
investigating on performance.
But this is the topic of another video.
Enjoy DAX.