Forum Discussion

zhona9's avatar
zhona9
Icon for Resolver I rankResolver I
3 years ago
Solved

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

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!

@measure filter
  • zhona9's avatar
    zhona9
    3 years ago

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

3 Replies

    • zhona9's avatar
      zhona9
      Icon for Resolver I rankResolver I
      3 years ago

      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's avatar
        zhona9
        Icon for Resolver I rankResolver I
        3 years ago

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