- 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!
