- Ciao, friends.
In this video, I want to show you
how to use VALUES to create complex filters
and solve a common problem
that is not as easy as one would expect.
Let's take a look first at the requirement.
I have a report that shows the year quarter
and then individual month in the same matrix.
I want to show a percentage,
which is not the percentage over the grand total
but it's the percentage of the current selection
against the total in the year.
So what I need to do is remove some filters
from my matrix but keep the filter on the year.
And I have a problem.
The year is not even present in my table.
So there is no filter context right now,
which is filtering the current year.
Let's start writing the code and then we reason on top of it
in the process of writing it.
Let's call the measure percentage.
We know it's a percentage, so it will be a division
and at the numerator, it's easy.
We just need to compute the sales amount.
At the denominator, we will need to use CALCULATE,
we compute sales amount
and then we need to REMOVEFILTERS.
Here we have a filter on the year and month
and we have a filter on the year and quarter.
The thing is I might also have other filters.
I don't know exactly what the user will use.
The year, the month, the quarter, whatever.
So I can start by removing the filters
from the entire date table.
I just REMOVEFILTERS from the date table.
I start writing this way
because I want to see the effect of my measure.
So I just hit Enter, format it as a percentage
and then I project it in my report.
It's easy to see that for quarter one 2007,
it provides 8%, 9%.
Let me collapse a few,
so we see we have a perception of the total.
You see the total of 2007 is very far from being 100%.
The reason is pretty clear.
I remove the filter from the entire date table.
The next step that I want to do
is keep the filter on the year.
This time, KEEPFILTERS is not going to help me
because it doesn't make sense
to do a KEEPFILTERS of a REMOVEFILTERS date.
REMOVEFILTERS will do exactly what it's expected to do.
It removes any filter from the date.
I need to create another filter,
which will be applied after REMOVEFILTERS date
that restores the filter on the year.
But how do I discover the year
which is currently selected
if the year does not even belong to my filter context?
There is a function, VALUES,
that returns the VALUES of a column
in the current filter context.
Now, if I evaluate VALUES of date year in this cell,
I will have a filter context containing
quarter one 2007, January 2007.
The only value visible for the year will be 2007.
Therefore, I can take the result of VALUES,
apply it to the filter context
and this will filter only 2007.
So what I need to do is add a new condition here
that says VALUES of date year.
This VALUES will restore the filter on the year,
even though the filter is not even present in the report.
And now you easily see that 23, 26, 25,
and 24 leads to 100%.
And my measure works no matter what I put in the filter,
in the report.
Whatever column I put from the date,
it will still compute the percentage in the year.
Keep in mind, the order in which you place this condition
has no meaning.
There is no change
if I apply first VALUES and later REMOVEFILTERS
because despite the fact
that we perceive them as one after the other,
the engine sees them at the same time.
What really matters here
is the order of evaluation of CALCULATE
but that's a too complex topic
for a short YouTube video.
Enjoy DAX!