Excluding Future Years from DAX Code

[WinterMist]

Hello,

Measure 7.1.3.1 correctly calculates the number of New Agents for each year in the Line Chart.

The only problem is that it includes future year 2024.

(This also causes the card to incorrectly show the 2024 value of 0, instead of the correct 2023 value of 14.)

Measure 7.1.3.2 should fix this (in my mind), but it doesn't.  Not only does it NOT remove 2024 as it should.

It also breaks the rest of the chart, by displaying the same value (from 2023) for all years.

I don't understand why.

Would someone guide me on how to modify either measure so that I continue to get the correct historical data (from 7.1.3.1) while excluding all future years (e.g. 2024) from the DAX measure?  2024 should be excluded from the measure so that it takes effect in any visual where used (both the line chart as well as the card).

If you could also educate me on why 7.1.3.2 doesn't eliminate 2024, as well as why it breaks all historical data, I'd appreciate the lesson.

Regards,

Nathan

[WinterMist]

@Wilson_ 

Thanks for your patience and ongoing explanations.

1) You are teaching me that Filter Context & X-axis labels are 2 completely different things. 

(Interesting.  I have always thought that X-axis labels were part of the filter context.  Although this doesn't make sense to me, because the year is certainly filtering the y-axis values differently for each year, I will memorize this anyway.)

2) You are teaching me that:

A) "the results of a measure cannot [possibly] affect what shows in the x-axis".

B) "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".

Again, I will memorize these things.  However, I am still confused by what I see.

I have 2 reports.

- The first report shares its dataset so that other reports can use it.

- The second report uses the shared dataset.

For the above thread, all of my measures & screenshots were from the first report.

When viewing the chart in report #1, 2024 is there.

However, when viewing the identical measure in report #2, 2024 is NOT there.

As you say, "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".

The thing is that there is not a single filter on the visual/page/report that pertains to this visual.

So.....

- It's not a filter on the visual/page/report.

- It's not a difference in the measure because it's a shared dataset.  (But this wouldn't matter anyway, right?  Even if the measure was different, it still couldn't possibly get rid of 2024 anyway.)

- It's not a difference in the data because it's a shared dataset.  (In fact, the visuals are identical in data, except for 2024 being present in one report and absent from the other.

In summary, not everything you are saying makes sense, but I am memorizing it anyway in hopes that it will make sense in the future.

More importantly, what Power BI is showing me in the second report contradicts what you are saying.  I'm still lost.

Regards,

Nathan

[Wilson_]

Nathan,

lmao I'm of course also open to my knowledge needing an update. If your empirical evidence is showing me I'm wrong, then obviously I have something else to learn. Always something more to learn with Power BI. I appreciate you poking holes in my understanding haha. 😄

If anybody who sees this in the future can clear this up for both of us, that'd be awesome. Maybe the fact that it's using an existing dataset vs being in the pbix with the dataset does actually make a difference for some reason. Maybe it's something else.

P.S.: To be clear about my original comment about filter context and x-axis labels being different things, I was not saying that x-axis labels aren't part of the filter context. I was merely asserting at the time that they were not synonymous (ie: while the x-axis affects the filter context, the filter context doesn't affect the x-axis).

[WinterMist]

@Wilson_  & @tamerj1 

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

[Wilson_]

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?)

[tamerj1]

Hi @WinterMist 

try to wrap the CALCULATE filter statement with KEEPFILTERS 

[WinterMist]

@Wilson 

Thanks for your response.

I modified 7.1.3.1 to just return the Result, but it doesn't work.

It returns BLANK for 2024 in both the Line Chart AND in the Card; which is the reason why I added the logic to return 0 instead of BLANK.

Regards,

Nathan

P.S.  Of lesser importance, I hear what you are saying as far as the explanation for 7.1.3.2, but it doesn't make sense based on how I've seen similar filters work within CALCULATE.  But thank you for trying to help me understand.

[Wilson_]

Nathan,

Is 'D Year'[PolicyYear_Int] your x-axis? If so, are there records in your 'D Year' table with a [PolicyYear_Int] of 2024? If so, that's possibly why it's behaving that way.

Also, my explanation for 7.1.3.2 makes perfect sense to me so if you were still interested, feel free to share any specific examples of similar filters working differently and I'll see if I can figure out the difference - because clearly there is one. 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 you wanted to add your filter condition to the filter context, try wrapping it in a KEEPFILTERS function.

For example, check out the below screenshot from this resource from SQLBI:

----------------------------------
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?)

[Wilson_]

Hey Nathan,

I believe if you just returned Result in measure 7.1.3.1, 2024 would not show up in the first visual.

Also, I believe 7.1.3.2 is breaking because no matter where you are on the x-axis, you're now overriding the filter on policy year with a filter that looks at all policy years 2023 and back. More concretely, no matter what year you look at on the x-axis, what the calculate in 7.1.3.2 is doing is returning all data from 2011 to 2023 instead of just that one year. Therefore, CY is always 2023 and PY is always 2022. That's why the second visual always returns 14, which is the calculation for 2023 in the first visual.

----------------------------------
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?)