How to use the equivalent of the Excel Countifs statement using DAX

[ConArm89]

Hi gang,

Im trying to get more involved with powerBI but it frsutrates me that the things I can do incredibly fast in excel, I cant seem to get to work in DAX.

Maybe some of you could help?

I have a few tables, all built with proper relationships by case #.

I need to count the amount of case #'s based on certain criteria.

example:

#                   Significant Case?           Type

Case 1                   Yes                          Environmental

Case 2                   Yes                          Injury

Case 3                    No                          Other

so if I wanted to create a measure that counted all of the cases that are significant and of type Environmental, what would I use?

So far I was trying to use something similar to this:

Case count = CALCULATE (COUNTROWS('table1), FILTER('table1','table1'[significant case?]="Yes"), FILTER('table1','table1'[type]="Injury"))

Basically I need to know what the proper format is to count a row, but only count based on multiple criteria. Some of the criteria area actually in a different table. bonus points if anyone can tell me how to do a filter based on only items from this year (one of the tables has a date column I could leverage).

Hopefully this makes sense?

Any help would be amazing. Thanks!

[v-juanli-msft]

Hi @ConArm89

Addition to Greg_Deckler's suggestion, to answer your question

"if I wanted to create a measure that counted all of the cases that are significant and of type Environmental, what would I use?"

You could use the following formula

CALCULATE (COUNTROWS('table1), FILTER('table1','table1'[significant case?]="Yes"&&'table1'[Type]="Environmental")

Best regards

Maggie

[Greg_Deckler · Accepted solution]

Should just be something like:

Measure = COUNTROWS(FILTER('table1','table1'[significant case?]="Yes" && 'table1'[type]="Injury"))

That being said, a potentially more flexible and useful way would be to just say:

Measure = COUNTROWS('table1')

And then put your two columns in separate slicers and now you have a generic measure that you can use with any criteria.