Forum Discussion

RichardJ's avatar
RichardJ
Responsive Resident
6 years ago
Solved

Syntax for Cumulative Count Measure with multiple Criteria

Hi,

I have this cumulative measure which works ok:

 

 

 

Cumulative Change Notes =
CALCULATE (
    COUNTA ( Company_Documents[ID] ),
    FILTER (
        ALLSELECTED ( Company_Documents ),
        Company_Documents[DOC_CREATION_DATE]
            <= MAX ( Company_Documents[DOC_CREATION_DATE] )
    )
)

 

 

However i'd like to limit the result by adding an additional criteria to the filter (starts &&)

 

 

Cumulative Change Notes =
CALCULATE (
    COUNTA ( Company_Documents[ID] ),
    FILTER (
        ALLSELECTED ( Company_Documents ),
        Company_Documents[DOC_CREATION_DATE]
            <= MAX ( Company_Documents[DOC_CREATION_DATE] )
            && Company_Documents[Document Class] = "Change Note"
    )
)

 

 

 

I must have the incorrect syntax as there are no records returned despite records being available which match the criteria.

Can anyone give me a pointer on where i've gone wrong when adding the additional filter criteria?

Thanks,
Richard

Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi RichardJ 

     

    Try this.

    Cumulative Change Notes =
VAR __maxCreationDate = MAX ( Company_Documents[DOC_CREATION_DATE] )
RETURN 
CALCULATE (
    COUNTA ( Company_Documents[ID] ),
    ALLSELECTED ( Company_Documents ),
    Company_Documents[DOC_CREATION_DATE] <= __maxCreationDate,
    Company_Documents[Document Class] = "Change Note"
)
    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi RichardJ

    Try this instead, if it does not work consider creating a data sample so we can test and apply formulas.

     
    Cumulative Change Notes =
    VAR __maxCreationDate = MAX ( Company_Documents[DOC_CREATION_DATE] )
    RETURN
    CALCULATE (
    COUNTROWS( Company_Documents ),
    FILTER(
    ALLSELECTED ( Company_Documents[DOC_CREATION_DATE], Company_Documents[Document Class] ),
    AND(
    Company_Documents[DOC_CREATION_DATE] <= __maxCreationDate,
    Company_Documents[Document Class] = "Change Note"
    )
    )​
     
    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

4 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi RichardJ 

     

    Try this.

    Cumulative Change Notes =
VAR __maxCreationDate = MAX ( Company_Documents[DOC_CREATION_DATE] )
RETURN 
CALCULATE (
    COUNTA ( Company_Documents[ID] ),
    ALLSELECTED ( Company_Documents ),
    Company_Documents[DOC_CREATION_DATE] <= __maxCreationDate,
    Company_Documents[Document Class] = "Change Note"
)
    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

    • RichardJ's avatar
      RichardJ
      Responsive Resident
      6 years ago

      Mariusz Many Thanks for the idea and the quick reply.

       

      This works perfectly.

       

      Initially I didn't think it had worked as I wasn't giving it enough time to calculate (circa 5 seconds) the response.


      Thanks again,

      Richard

      • Mariusz's avatar
        Mariusz
        Community Champion
        6 years ago

        Hi RichardJ

        Try this instead, if it does not work consider creating a data sample so we can test and apply formulas.

         
        Cumulative Change Notes =
        VAR __maxCreationDate = MAX ( Company_Documents[DOC_CREATION_DATE] )
        RETURN
        CALCULATE (
        COUNTROWS( Company_Documents ),
        FILTER(
        ALLSELECTED ( Company_Documents[DOC_CREATION_DATE], Company_Documents[Document Class] ),
        AND(
        Company_Documents[DOC_CREATION_DATE] <= __maxCreationDate,
        Company_Documents[Document Class] = "Change Note"
        )
        )​
         
        Best Regards,
        Mariusz

        If this post helps, then please consider Accepting it as the solution.

        Please feel free to connect with me.
        LinkedIn