Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

using two filters one with text and one date

So I need a measure which shows a count of members who have raised a case in the current month, who have an eligibility flag of "yes"

So I can do a distinct count of member ID for the number of members, and then filter on case creation date looking at the current month and year, but when I try to add a filter for eligibility = "yes" my measure kicks off saying I cannot use a True/False expression that is used as a table filter expression.

 

Here is my incorrect attempt:

Eligible Members this Month = CALCULATE(DISTINCTCOUNT('Members'[MemberID]),MONTH('Case'[Createdon])=month(TODAY())&&YEAR('Case'[Createdon]=YEAR(TODAY())&&FILTER('Members','Members'[Eligible]="Yes")))

Is there anything I can do within the measure to resolve this?

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Riantilo  you almost had it, without closing off the &&Year with three close brackets the measure believes the final filter is for the YEAR argument. 

    So the below works! Thanks for your help! :-) 

    Eligible Members this Month =
CALCULATE(
DISTINCTCOUNT('Members'[MemberID]),
MONTH('Case'[Createdon])=month(TODAY()) &&
YEAR('Case'[Createdon]=YEAR(TODAY())) ,
FILTER(
'Members',
'Members'[Eligible]="Yes"
)
)

3 Replies

  • Riantilo's avatar
    Riantilo
    Frequent Visitor
    7 years ago

    I think u can fix this bij changing the last && to a , 

    This should work:
    Eligible Members this Month =
    CALCULATE(
    DISTINCTCOUNT('Members'[MemberID]),
    MONTH('Case'[Createdon])=month(TODAY()) &&
    YEAR('Case'[Createdon]=YEAR(TODAY()) ,
    FILTER(
    'Members',
    'Members'[Eligible]="Yes"
    )
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Riantilo  you almost had it, without closing off the &&Year with three close brackets the measure believes the final filter is for the YEAR argument. 

      So the below works! Thanks for your help! :-) 

      Eligible Members this Month =
CALCULATE(
DISTINCTCOUNT('Members'[MemberID]),
MONTH('Case'[Createdon])=month(TODAY()) &&
YEAR('Case'[Createdon]=YEAR(TODAY())) ,
FILTER(
'Members',
'Members'[Eligible]="Yes"
)
)
      • Riantilo's avatar
        Riantilo
        Frequent Visitor
        7 years ago

        I copied the code from the original post. And dit not see that mistake. 

        YEAR('Case'[Createdon]=YEAR(TODAY()))

         That explains why it didn't work.

         

        to explain the error: The FILTER function can only be used in its own CALCULATE filter expression and can not be combined with the && or || operators.