Forum Discussion
Excluding Future Years from DAX Code
1) Thanks for the reminder to use KEEPFILTERS. However, I'm still doing something wrong.
So I need to KEEPFILTERS for all of the years which are <= the current year, while at the same time, overwriting the original filter context to remove all of the years > the current year (e.g. 2024). Is there a way to do both?
2) Yes. 'D Year'[PolicyYear_Int] is the X-axis. And it makes sense to me that 2024 appears in the chart BEFORE overwriting the filter context; because, just as you say ( Wilson_ ), 2024 appears in the 'D Year' table (due to next year's estimates). However, you also state, and I agree 100% that:
"The important thing of note (to me, anyway) is understanding filter conditions in the CALCULATE function override existing filters on those fields as a default behaviour."
If that's true (and I believe it is), then it makes no sense to me at all that 2024 is still appearing in the chart AFTER the filter condition in the CALCULATE statement overwrites it.
NOTE: I realize that KEEPFILTERS needs to be used for the actual solution. I am merely writing point #2 to explain why I am so confused about the CALCULATE filter NOT overwriting the original filter context when it should.
This filter condition explicitly overwrites the original filter context (which included 2024).
And yet somehow, 2024 (which should be overwritten with <= 2023) is NOT overwritten and still appears.
How is this possible?
Either the CALCULATE filter overwrites the original FC, or it doesn't.
Our dear friends Alberto & Marco (who know everything about DAX) teach us that it DOES.
But this example shows that it DOESN'T.
Clearly I am missing something significant.
Even if I use ALL or REMOVEFILTERS to explicitly remove the original FC for 'D Year', the result is the same.
2024 will NOT disappear from the chart.
I appreciate both of your efforts to help me.
But I still do not understand.
Regards,
Nathan
Hi Nathan,
re:
I am merely writing point #2 to explain why I am so confused about the CALCULATE filter NOT overwriting the original filter context when it should.
This filter condition explicitly overwrites the original filter context (which included 2024).
And yet somehow, 2024 (which should be overwritten with <= 2023) is NOT overwritten and still appears.
How is this possible?
I understand your confusion. It's tricky. Let me try this a different way. I think you're conflating two different things here. You have the evaluation context/filter context of the measure and you also have the x-axis labels. Those are two completely separate things. What I mean by that is the x-axis is merely displaying all the values you have for 'D Year'[PolicyYear_Int] first, then calculating your measure to display in the y-axis. The results of your measure cannot affect what shows in the x-axis.
From the above in your reply, you can see that the KEEPFILTER is doing its job because it's calculating a blank for 2024! It is both respecting the initial filter context of "policy year = 2024" and layering in the additional filter context of "policy year <= 2023" in, which logically will always return an empty table to calculate off. Again though, this has nothing to do with the fact that it's 2024 is being displayed in your x-axis at all. It is showing 2024 in your x-axis, then calculating the measure and returning a blank result - but that won't remove it from your visual.
Said differently, unless you add a filter to the visual/page/report, there is literally nothing you can do to remove 2024 from the x-axis through DAX. This is showing up not because of your measure; this is showing up simply because it exists in the column 'D Year'[PolicyYear_Int]. What you write into your DAX measure merely affects what value shows up on the y-axis for each value between 2010 and 2024.
(For what it's worth, what I end up doing if I don't want future years to show up is to add the CALCULATE filter like you have, then filtering for the measure being a non-blank in the filter pane.)
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)