- Ciao friends.
In this video, I want to talk
about the granularity of a formula.
And specifically, I will show you how to compute values
below the granularity of the formula.
We will first understand what we mean
by granularity of a DAX expression.
The granularity of a formula is one of the important
DAX concepts that most DAX newbies
do not take into account the right way.
And this is a pity because understanding
the granularity of a formula and understanding
what to do above and below that granularity
is very important in order to produce calculation
that always compute the correct value.
And if they cannot compute the correct value,
they simply blank out the result,
but never show the wrong number.
It's much easier to understand the concept of granularity
if we go straight to the demo, so let's do that.
You see, in this report I'm showing the year, month,
and the value of sales amount.
Then I created a formula, which is very simple.
And it only serves the purpose of this demo,
I want to check whether a month has sales
which are greater than 15,000.
You see that this happens in all the months in 2007,
except in August where the sales amount is below 15,000.
A first version might be as easy as this,
is high value that checks if the sales amount
is greater than 15,000 returns one,
otherwise, it returns a blank.
This formula does not work.
And you can see that it's not working
because it produces a correct value month by month,
but it does not produce a correct value at the year level.
This is a common scenario and we already created videos
on how to solve it, what you need to do is simply iterate
month by month in order to force the calculation
at the grain of the month.
And I already did that in the high month measure
that you see shows the correct result 11,
still the same value month by month.
And to do that it iterates over the year month,
and computes the measure at the correct grain.
What I did is, I defined the granularity of a formula
stating that my formula works at the month level.
And when I'm above the month level, at the year for example,
what I do is sum the values computed month by month.
But most important, I define the granularity of the measure
that now works at the month level.
What happens if I go below the granularity,
because we have seen that above the grain,
everything is working fine, but what about below?
Let's expand January, you see it shows a blank everywhere.
Because no day in January has a sales amount
which is greater than 15,000.
But this is not always the case.
If we searched for a month with a larger amount, like April,
in April, it starts showing blank everywhere
except on the 17th of April, where it shows one.
And the reason is, in the single day of April 17, 2008
I have sold more than 15,000.
The thing is this number is wrong.
Because I'm not computing the calculation
at the correct grain which is the month,
I'm computing at the month level
when I'm above the month, but then I go at the day level
if I am below the month.
What I need to do is first choose what to do,
do I want to show one everywhere
because April in total has more than 15,000.
So day by day, I always show one because
I'm computing the value at the granularity of the month
or I blank out the measure, so I do not show this one,
because I know that I went below the granularity
supported by my formula and therefore
I cannot compute the number, we will see both solutions.
And while seeing the solution we will also
pay attention to several small details.
Because it is utterly easy to make mistakes
writing this code if you do not pay attention
to how the filter context, the row context
and context transition happened in writing your code.
Let's do that together.
Let's first solve the problem
of showing one everywhere.
What I want to do is propagate the value
at the month level also at the day level.
The code as it is now does not work because
when I'm at the month level
VALUES of date, calendar, year month returns only one row
and this computes the value at the month level.
But when I go below it at the day level,
here the filter context is containing one day only.
Therefore my is high value is computing the sales
of one day, I need to get rid of the filter context
on the day and recreate the filter context on the month.
Let me do that on a different measure
so we can see the different results.
And let's call it high months two.
As I said, I need to remove the filter context
from the day and recreate
the filter context on the month.
So I need CALCULATE, compute my measure,
I REMOVEFILTERS from the date
and I can use VALUES of date calendar year month.
This version will not work.
And the reason is I'm using a pattern to remove a filter
and recreate a filter which is not the correct one.
Let's give it a try, if I put high months here,
it shows one everywhere month by month
but look at the total here,
it shows 12 instead of showing 11.
Therefore the formula is not computing the correct number.
I'm not recreating the correct filter context.
And also the total here 84 is clearly a wrong number.
Let me expand this a bit otherwise, I don't see the numbers.
The reason is, this REMOVEFILTERS
removes the filter from the date and this VALUES
recreates the filter on the year month.
But that should be a reference
to the currently iterated value of year month
which is happening because of SUMX.
So SUMX is iterating over the year month,
and because of context transition,
here I will see only that year month.
But remember, context transition happens
during the calculation of the measure.
But it does not happen when you calculate the filters.
So either you go for something really long
and weird, like adding another CALCULATETABLE here
to force context transition.
And if I write it this way, I obtain the correct results,
35, 11 and all the numbers are shown correctly one.
But it's actually easier to avoid using the pattern
of REMOVEFILTERS and VALUES, and instead use ALLEXCEPT.
If you go for ALLEXCEPT date, and you do ALLEXCEPT,
the date calendar year month,
by using ALLEXCEPT I remove the filter from anything,
from the date except a filter on the year month.
And this computes the correct number using ALLEXCEPT
instead of using the pattern of ALL date.
So you have seen that in order to compute the value
at the lower level of granularity,
I need to recreate the filter that was above
at the correct level of grain.
The grain of the calculation is the month,
I need to recreate the filter at the month level.
That was the first option, so showing one on all the rows.
Let's go for the other option that requires hiding the value
when I go below the granularity supported by my calculation.
To do that, we need to pay attention to further details,
we need to understand when the user
by browsing the code has gone below the granularity
supported by my formula.
And to do that, I will basically compute the number of days
which are visible in the current filter context,
the number of days which are visible in the filter context
containing the entire month, and I compare the two values.
If they are not the same, it means I am at a granularity
that is not supported by my formula, let's do that together.
Let me copy the code of this measure
so we have a starting point,
and we don't have to rewrite everything.
And let's call this high months three.
So first, we need to understand how many days are visible
in the current filter context.
So we create the variable days at current grain,
and this is just a COUNTROWS of date.
These are the number of days visible
in the current filter context.
It's probably better if we return the variables one by one,
so we understand better the algorithm.
So let's start by looking at what...
what days at current grain computes.
And you see that it shows one at the day level,
31 at the month level, 365 at the year level.
Now we want the number of days
at the granularity of the formula.
So we create another variable that we call
days at formula grain.
And to do we use CALCULATE.
We compute again COUNTROWS
COUNTROWS of date.
We can use the same pattern we used earlier,
ALLEXCEPT, date, date,
calendar year, calendar year month.
Oh, okay, now let's see what this part returns.
And it's wrong, you see it always return a large number,
the value of the grand total for all the rows, why that?
Again, I did not pay attention to row context,
filter context, context transition.
The same formula that was working earlier
is not expected to work here.
Because here, I cannot use ALLEXCEPT,
here I need to use the pattern with REMOVEFILTERS
over date, and then VALUES of date calendar year month.
Because here I now have no context transition
happening earlier, therefore, I need to recreate
the filter context the right way.
And if I do that, now, with the correct formula,
you'll see that I have 31, 365
and 31 is repeated day by day.
So this is the number of days in the current month.
Now that I have my two calculations,
I only need to check whether I can compute the value.
So I create a third variable is safe to compute,
that tells me if days at current grain is equal to the days,
days at formula grain.
Let's get the result of is safe to compute.
Now, it shows true when I'm showing January
and false when I go at the day level.
And it also shows true at the year level.
So whenever I am at the formula grain or above,
my calculation shows true.
Last thing that I need to do is just compute the result.
The result says IF is safe to compute.
And now I can repeat the same code that I did earlier,
SUMX over the values of date
calendar year month
of is high value.
And finally I return the result.
So it's the same code as the original formula,
but now it's protected by my variable is safe to compute.
Let's reduce the zoom a bit
so you can see the entire piece of code.
And now my high months three returns one in January
11 at the year level, 35 at the grand total.
And if I did everything correctly,
when I go in 2008 in April,
it will show blanks also on the 17th of April.
You see that the high month,
the original measure shows one here, my high months measure,
instead shows a blank as it is expected.
The important details here is that once you understand
how to correctly compute values
at different granularities,
you are able to make your formula behave correctly,
at the formula grain, above the formula grain
and below the formula grain.
It is important that you understand well
how to compute values at different levels of granularity.
As you have seen you need to pay attention,
sometimes you use ALL VALUES sometimes you use ALLEXCEPT,
it all depends on the formula you wrote,
how the filter context is shaped
and what the final result you want to obtain is.
Mixing these things together
is the key to compute the correct value.
If these concepts are not completely clear,
that basically means you still need to practice a bit more
with the fundamentals.
DAX is all about row context,
filter context and context transition.
If some of the code in these demos was not entirely clear,
go back to the basics, read again the chapters about
the evaluation context and... put them in your mind
and let them sink the right way
so that you have a solid understanding of the basics.
Once that happens, DAX will have no secrets for you.
Enjoy DAX.