cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tvarberg
Helper I
Helper I

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)

 

 

Capture.PNG

1 ACCEPTED SOLUTION
AlB
Super User
Super User

@tvarberg 

= 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")

 

 

SU18_powerbi_badge

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.

 

View solution in original post

3 REPLIES 3
AlB
Super User
Super User

@tvarberg 

= 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")

 

 

SU18_powerbi_badge

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.

 

tvarberg
Helper I
Helper I

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
Super User
Super User

Hi @tvarberg 

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

 

SU18_powerbi_badge

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.

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!