topic Re: Filtering a measure with a measure isn't working in DAX Commands and Tips

Filtering a measure with a measure isn't working

peck3472 — Tue, 28 Feb 2023 16:01:11 GMT

I have a table of data that contains a column for date and a column for maintenance hours. I'm trying to use a measure to get the point closest to today's date that is either today or after today.

I use the following to get the date of the next point (this is working fine):

NextDatePoint = CALCULATE(MIN('report_maint_forecast'[point_date]), report_maint_forecast[point_date]>=TODAY() && report_maint_forecast[point_type]="Jan_2023_Baseline"&&report_maint_forecast[region]="Payette")

Next I use the previous measure (the date) within my new measure to try to get the maintenance for that date:

NextMaintPoint = calculate(max(report_maint_forecast[point_remaining_maint]), filter(report_maint_forecast,report_maint_forecast[point_date]=

[NextDatePoint] && report_maint_forecast[point_type]="Jan_2023_Baseline"&&report_maint_forecast[region]="Payette"))

This isn't working, it returns a value but it's the incorrect value. When I change my measure from MAX to COUNT, like below, it shows I'm getting 11 records, there's only 1 record in the data that matches that filter.

NextMaintPoint = calculate(count(report_maint_forecast[point_remaining_maint]), filter(report_maint_forecast,report_maint_forecast[point_date]=

[NextDatePoint] && report_maint_forecast[point_type]="Jan_2023_Baseline"&&report_maint_forecast[region]="Payette"))

To further troubleshoot I try defining a table, that just returns the filter portion. It's returning 11 rows, but the date doesn't match [NextDatePoint], it's as if it's filtering using the data before the MIN was applied to the NextDatePoint measure:

To further troubleshoot I tried doing everything within one measure as follows, this works:

AllinOneCalc =

var NDPoint = CALCULATE(MIN('report_maint_forecast'[point_date]), report_maint_forecast[point_date]>=TODAY() && report_maint_forecast[point_type]="Jan_2023_Baseline"&&report_maint_forecast[region]="Payette")

RETURN

calculate(count(report_maint_forecast[point_remaining_maint]), filter(report_maint_forecast,report_maint_forecast[point_date]= NDPoint

&& report_maint_forecast[point_type]="Jan_2023_Baseline"&&report_maint_forecast[region]="Payette"))

Any help is appreciated! Thanks!

Re: Filtering a measure with a measure isn't working

andhiii079845 — Tue, 28 Feb 2023 16:08:37 GMT

Do you try to check in what is in "NextDatePoint"

NextMaintPoint = calculate(max(report_maint_forecast[point_remaining_maint]),  filter(report_maint_forecast,report_maint_forecast[point_date]=
[NextDatePoint] && report_maint_forecast[point_type]="Jan_2023_Baseline"&&report_maint_forecast[region]="Payette"))

like this. Try to use also VAR in our regular measure.

Test =
Var __t = NextMaintPoint 
Return __t

Re: Filtering a measure with a measure isn't working

peck3472 — Tue, 28 Feb 2023 16:16:43 GMT

Hi. Thanks for your help. Yes, I checked the value in NextDatePoint and it is correct.

I also tried using Var, and that didn't seem to work either:

NextDatePoint =

Var _T = CALCULATE(MIN('report_maint_forecast'[point_date]), report_maint_forecast[point_date]>=TODAY() && report_maint_forecast[point_type]="Jan_2023_Baseline"&&report_maint_forecast[region]="Payette")

Return _T

Re: Filtering a measure with a measure isn't working

andhiii079845 — Tue, 28 Feb 2023 16:30:46 GMT

I mean like this:
NextMaintPoint =
Var __t = NextMaintPoint
RETURN calculate(max(report_maint_forecast[point_remaining_maint]), filter(report_maint_forecast,report_maint_forecast[point_date]=

__t && report_maint_forecast[point_type]="Jan_2023_Baseline"&&report_maint_forecast[region]="Payette"))

Re: Filtering a measure with a measure isn't working

tamerj1 — Tue, 28 Feb 2023 16:23:36 GMT

Hi peck3472

please try

NextMaintPoint =
VAR NextDatePoint = [NextDatePoint]
RETURN
CALCULATE (
MAX ( report_maint_forecast[point_remaining_maint] ),
FILTER (
report_maint_forecast,
report_maint_forecast[point_date] = NextDatePoint
&& report_maint_forecast[point_type] = "Jan_2023_Baseline"
&& report_maint_forecast[region] = "Payette"
)
)

Re: Filtering a measure with a measure isn't working

peck3472 — Tue, 28 Feb 2023 16:24:06 GMT

Just tried that, it's also not returning correctly.

NextMaintPoint =

Var _T= calculate(count(report_maint_forecast[point_remaining_maint]), filter(report_maint_forecast,report_maint_forecast[point_date]=[NextDatePoint] && report_maint_forecast[point_type]="Jan_2023_Baseline"&&report_maint_forecast[region]="Payette"))

Return _T

Re: Filtering a measure with a measure isn't working

peck3472 — Tue, 28 Feb 2023 16:26:34 GMT

Hi tamerj1, that worked! Do you think that is how I should do it anytime I use a measure within a measure?

Re: Filtering a measure with a measure isn't working

andhiii079845 — Tue, 28 Feb 2023 16:30:27 GMT

You copy my measure wrong, but know you have the solution 😉

Re: Filtering a measure with a measure isn't working

tamerj1 — Tue, 28 Feb 2023 16:31:03 GMT

peck3472

You need to be extremely careful when referring to a measure inside an iteration function (FILTER in your case) as context transition happens, therefore, the measure is reevaluated within each iteration converting the row context into filter context. In other words it is evaluated differently for each row.

Re: Filtering a measure with a measure isn't working

peck3472 — Tue, 28 Feb 2023 16:36:25 GMT

Hi andhiii079845, you're right, I did copy it wrong. Yours also worked after I changed "Var __t = NextMaintPoint" to "Var __t = [NextDatePoint]" Thank-you for you help, I really appreciate it!

Re: Filtering a measure with a measure isn't working

peck3472 — Tue, 28 Feb 2023 16:37:28 GMT

Thanks tamerj1! I really appreciate the help!