Error: A function 'FILTER' has been used in a True/False expression that is used as a table filter

[zhona9]

I am trying to get the upcoming milestone in a measure.

I have a working measure for the last completed milestone that is quite similar, this is the code of the last completed milestone, which is working fine:

LastCompletedMilestone = 
var currentproject = selectedvalue('CI Milestones'[ProjectID0])

RETURN
CALCULATE(MAX('CI Milestones'[Title]),
'CI Milestones'[Id]=
MAXX(
    FILTER(ALL('CI Milestones'),'CI Milestones'[ProjectID0]=currentproject && 'CI Milestones'[MilestoneStatus]="Completed"),
    'CI Milestones'[Id])
)

This is the code I have for the upcoming milestone, which throws me an error "A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed".

Upcoming Milestone = 
var currentproject = selectedvalue('CI Milestones'[ProjectID0])

RETURN
CALCULATE(MIN('CI Milestones'[Title]),
'CI Milestones'[Id]=
MIN(
    FILTER(ALL('CI Milestones'),'CI Milestones'[ProjectID0]=currentproject && 'CI Milestones'[MilestoneStatus]<>"Completed" && 'CI Milestones'[MilestoneStartDate]>TODAY()),
    'CI Milestones'[Id])
)

I cannot figure out why this new measure has an error. Kindly assist!

[FreemanZ]

hi @zhona9 

try first changing the second MIN to MINX in your code?

[zhona9]

Thanks for the suggestion. However I encounter the same error. A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Upcoming Milestone = 
var currentproject = selectedvalue('CI Milestones'[ProjectID0])

RETURN
CALCULATE(MIN('CI Milestones'[Title]),
'CI Milestones'[Id]=
MINX(
    FILTER(ALL('CI Milestones'),'CI Milestones'[ProjectID0]=currentproject && 'CI Milestones'[MilestoneStatus]<>"Completed" && 'CI Milestones'[MilestoneStartDate]>TODAY()),
    'CI Milestones'[Id])
)

[zhona9 · Accepted solution]

I removed one of the conditions and now it works:

Upcoming Milestone = 
var currentproject = selectedvalue('CI Milestones'[ProjectID0])

RETURN
CALCULATE(MIN('CI Milestones'[Title]),
'CI Milestones'[Id]=
MINX(
    FILTER(ALL('CI Milestones'),'CI Milestones'[ProjectID0]=currentproject && 'CI Milestones'[MilestoneStartDate]>TODAY()),
    'CI Milestones'[Id])
)