Filtering a measure with a measure isn't working

[peck3472]

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!

[andhiii079845 · Accepted solution]

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

[tamerj1 · Accepted solution]

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

[peck3472]

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

[tamerj1]

@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. 

[peck3472]

Thanks @tamerj1!  I really appreciate the help!

[andhiii079845 · Accepted solution]

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

[peck3472]

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

[andhiii079845]

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

[peck3472]

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!

[andhiii079845]

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  

[peck3472]

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