Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count with multiple criteria

I can see this question has been asked before. Somehow it's not working.

I want to count from "open/closed status" and "phase"

 

Open/close status criteria = "open" and "closed"

Phase = "4 - Delivery"

 

I have managed to search with one criterion only.

CALCULATE(COUNTROWS(Project), SEARCH("closed", Project[open/closed Status],,0)>0)

 

 

  • AlB's avatar
    AlB
    5 years ago

    Anonymous 

    = CALCULATE(
        COUNTROWS(Project),
        Project[open/closed Status] IN  {"Open", "Closed"},
        Project[Phase] = "4 - Delivery",
        Project[Est. Execution/Project start date] >= DATEVALUE("01-01-2020"),
        Project[Est. Execution/Project start date] <= DATEVALUE("31-12-2020")
)

    You can also use DATE(2021,12,31)  instead of DATEVALUE("31-12-2020")

     

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

3 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Anonymous 

    The condition is not completely clear. Try:

    CALCULATE (
    COUNTROWS ( Project ),
    Project[open/closed Status] IN { "open", "closed" },
    Project[Phase] = "4 - Delivery"
)

    or

    CALCULATE (
    COUNTROWS ( Project ),
    Project[open/closed Status] = "closed",
    Project[Phase] = "4 - Delivery"
)

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi AlB 
    This is working perfectly.
    I also a have column with dates I would like to include e.g. >= 01-01-2020 <=31-12-2020 - can this also be done?

    = CALCULATE(
        COUNTROWS(Project),
        Project[open/closed Status] IN  {"Open", "Closed"},
        Project[Phase] = "4 - Delivery",
        Project[Est. Execution/Project start date] >= "01-01-2020" & <= "31-12-2020"
)
  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous 

    = CALCULATE(
        COUNTROWS(Project),
        Project[open/closed Status] IN  {"Open", "Closed"},
        Project[Phase] = "4 - Delivery",
        Project[Est. Execution/Project start date] >= DATEVALUE("01-01-2020"),
        Project[Est. Execution/Project start date] <= DATEVALUE("31-12-2020")
)

    You can also use DATE(2021,12,31)  instead of DATEVALUE("31-12-2020")

     

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.