COUNTIFS in Power BI

[Anonymous]

Hey folks,

I apologize in advance because there are numerous threads on this topic, but each solution to those threads is so specific to the asker's situation that I couldn't grasp the logic behind how this works.

Basically, the situation is simple: I just need to have a DAX measure that looks through my data and returns a count based on two criteria. The first criteria is a Completion Status (of which there are only 3 options, "Complete", "In Progress", and "Not yet started"), and the second criteria is Department (for this example let's say "Production").

I can't for the life of me get this to work. The measure I tried after researching online was:

COUNTIFS = 
CALCULATE (
    COUNTROWS (course_completions_report),
    FILTER (
        course_completions_report,
        course_completions_report[Department] = "Production"
            && course_completions_report[Completion Status] = "Complete"
    )
)

but that returns a blank value when it should return a value of 28,723.

I'm very new to Power BI but above average at Excel, but I can't figure out how the syntax here lines up against COUNTIFS to make it work. I'm attaching an image in case that helps as well.

I'd obviously love to get this working with the proper code, but if anyone also has the time to briefly explain the syntax here (bonus points if you can explain it in a way an Excel nerd can understand) so I can use this moment to learn and avoid asking stuff like this in the future, I'd really appreciate it.

[Nathaniel_C · Accepted solution]

Hi @Anonymous ,

COUNTIFS = 
var _prod = "Productions"
var _comp = "Complete"


CALCULATE (
    COUNTROWS (course_completions_report),
    FILTER (
        ALL(course_completions_report),
        course_completions_report[Department] = _prod
            && course_completions_report[Completion Status] = _comp
    )
)

Try this:

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Anonymous]

Apologies, your solution actually worked perfectly, I just had the column header titled incorrectly. Thanks so much! 😄

[Nathaniel_C]

Hi @Anonymous ,
Good job debugging! You are welcome. If like me, you come from an Excel background, let me recommend two books.
M is for (Data) Monkey by @KenPuls ,
Supercharge Power BI by @MattAllington . 
Both books are well written and will go a long way to making DAX and Power Query part of your set of tools. Once through those, check out SQLBI.com and their videos.

Good luck!

Nathaniel

[Anonymous]

Thank you for such a prompt response! 

I tried your code but get the following error:

I'm not sure what that means, so I wasn't able to troubleshoot beyond taking the screenshot. Any thoughts?