- Ciao, friends.
In this video, I want to analyze with you the performance
of LASTNONBLANK and write the same calculation
in several different ways
until we find the best way to author our calculation.
So we will optimize LASTNONBLANK
but keep in mind that the takeaway of the video
is not how to write LASTNONBLANK in the best possible way.
Instead, I want you to see different ways
of computing the same calculation
and how we choose the best one.
You need to be prepared to perform the same test
in your specific model
because your findings might be different,
depending on data distribution, cardinality of columns,
a lot of important aspects.
So learning how to optimize code
is one of the important skills for DAX developers,
if you care about performance at all.
Now, let's get started by looking at the scenario.
Then we focus on one formula and we start optimizing it.
Let's do that.
I want to build this report.
I'm filtering only one company.
And I have the sales day by day.
I have the sales amount, the number of orders
and I want to compare the sales amount
with the sales in the previous day.
So you see that we have on the 14th of August this amount
that is then reported here as the previous day
of the 22nd of August.
When we say previous day, we do not mean the day before.
We mean the previous day in the given filter context.
Because we are filtering only one company,
the previous day is the day
where this company made its previous order.
So the calculation needs to be dynamic.
It's not just the date minus one.
That would be utterly simple.
So I want to compare the sales
with the previous day and then compute the difference
and do whatever calculation.
The complexity of this report is not in the difference,
it's all in the calculation of the previous day
because we need to search given the current context
for the previous day where there were sales.
So let's look at the code and then we start to optimize it.
We're going to look at the code using DAX Studio.
So I already have the query prepared here
with sales previous date,
which is the measure we are interested in.
Sales previous date first retrieves the first visible date.
So given the current filter context,
it retrieves the first day in the filter context
and then it uses LASTNONBLANK
because it needs to search for the previous date
where there were sales.
So it uses LASTNONBLANK over date,
searching for the last date
where the sales amount is not blank
for all the dates that happen
to be before the first visible date.
What it returns is the previous order date.
So the date where we had the previous order.
Finally, to compute the sales in the previous day,
we use CALCULATE sales amount using previous order date
as a filter.
Therefore, it computes the sales in the previous date
when there was an order.
And then I use SUMMARIZECOLUMNS to group by date
and compute the sales in the previous date.
I'm not interested in the result.
I'm actually interested in running the query
and look how long it takes.
We take a look at the result first.
You see that we have some results.
We have by date the sales in the previous date.
And in the server timings, we can look at how long it took.
It took 2.1 seconds, 2.2 seconds, around 2 seconds
and the important details here
is that the storage engine CPU is basically nonexistent.
It's not doing any kind of work in storage engine.
The entire calculation is pushed to formula engine.
Indeed, you see that out of 2.18 seconds,
we have 2.16 seconds of formula engine.
That means we are not interested in looking at those queries
because they are not important
and even though we have a CallbackDataID here,
we do not care at all
because the entire calculation is in formula engine.
So we need to write the code in different ways,
trying to make formula engine do a better job.
What can we do? Well, the first consideration
is that we are not actually interested in the date
or what was the previous day.
We are interested in the sales amount of the previous day.
Now, DAX offers you LASTNONBLANK to search
for the last date where a measure is not blank
but it also offers you another function,
LASTNONBLANKVALUE, that immediately returns the value
instead of returning the date.
Maybe LASTNONBLANKVALUE is going
to provide better performance.
We need to test it out. So let's do that.
Instead of using LASTNONBLANK,
we can use LASTNONBLANKVALUE.
And if we use LASTNONBLANKVALUE,
the result will no longer be a date.
It will be the value of the sales amount.
So we don't need the previous order date.
We can name this variable result
and get rid of this part.
So the code is a bit simpler now.
We retrieve the LASTNONBLANKVALUE, sorting by date
where the date is before the first visible date.
So let's remember 2.18 seconds is the starting point.
I have an error.
Yes, this is no longer CALCULATETABLE.
Now it's CALCULATE.
2.1 seconds, it goes to 1.9 seconds.
So using LASTNONBLANKVALUE provides
a bit better performance.
It's not super fast but it's a bit better
because we made the code a bit simpler.
But the thing is, can we do anything better than that?
We first moved away from computing first date
and then the value but still,
we are using LASTNONBLANKVALUE.
LASTNONBLANKVALUE, in order to compute the value,
it needs to compute the sales amount date by date
and finally, return the last value
for which this result was non-blank.
But we do not need to compute the sales amount date by date.
We can make an assumption on the data model saying well,
if there are any data in the sales table,
then I will consider that as the last date
because if there is data in sales, it means there are sales.
So we are making a stronger assumption
but we avoid computing sales amount day by day
and we compute it only at the end.
Let's do that.
We need to go back to the previous calculation.
We still need the first visible date
and we want not LASTNONBLANKVALUE,
we search for the last date in the sales table,
which is before the current date.
So we search for the MAX of sales order date
where the date is less than the first visible date.
Now, this is no longer the result.
We called this measure...
previous order date.
And then we need to go back to the previous calculation.
That was CALCULATE sales amount
and we place a filter on the date date
that needs to be equal to the previous order date.
There are some details which are worth discussing here.
First, you see that we are retrieving
the maximum sales order date
where the date is before the first visible date.
And then we compute the sales amount,
applying the filter over the date table.
Is it going to be faster or slower?
Well, we don't know that. We can give it a try.
Let's run it.
It's actually faster.
You see, it now takes 1.2 seconds instead of
1.9 seconds that was executing first earlier.
The reason is, we no longer need
to compute sales amount for all the rows.
Now we can simply compute the sales amount at the end
after we have scanned the sales order date,
searching for the last date where we have a calculation.
The thing is, can we do any better than that?
Well, to obtain better performance,
we need to investigate a bit more on the code.
Look at what is happening here.
I'm computing the maximum sales order date
and then because what I obtain is a date,
I apply the filter on the date column in the date table
and I need to do that
because I might have other filters on the date
and I want to get rid of them,
replacing with the filter on the date.
Can we avoid filtering date?
We can actually filter straight sales order date,
avoiding to go on the date table.
So instead of filtering here date date,
we can filter sales order date.
The thing is, if we do that,
so if we do the calculation
over sales order date previous order date,
we also need to get rid of any filter on the date.
So now this calculation happens only in the sales table.
And we can do the same here.
Why do I apply the filter on the date date,
where we compute the maximum on the sales order date?
We can actually apply the filter on sales order date,
which is less than the first visible date
but exactly as we did earlier,
we need to get rid of any filter on the date
because we are replacing the filter
with the new filter we are computing.
Let's use REMOVEFILTERS both places.
The code, again is going to compute the same number.
We don't know if it's going to be faster or not
but we can give that a try.
Let's run it.
And it's actually much faster.
Now it runs in around 600 milliseconds.
We moved from 2.1 seconds to 600 milliseconds
just by changing the way the calculation happens.
Beware that the entire calculation
is still in formula engine.
You see here the entire calculation is carried on
in formula engine, so this is not going
to be the fastest measure ever.
But cutting more than half of the time
is already a very good result.
As you have seen, experimenting different ways
of computing the same expression
provides different performance.
You need to be ready to do that with DAX.
Do not just stick with one version of your code
because it might be that the best version
is a different way of obtaining the same result.
In order to understand how DAX solves the query,
what you need to do is well, first, learn about it,
learn about optimizing DAX.
Learn about the storage engine, the formula engine,
how to read a query plan
and how to correctly interpret the server timings.
Once you have that,
then you can test the same code again and again
until you find the best way of expressing your code.
Enjoy DAX.