COUNT IF Measure in Query Editor

[Anonymous]

So I have a column which holds a text string which is populated from a list of options the user can select from. The column holds an error reason. I want to calculate total errors based on this column.

There is an error reason of "No Error" so I would want these excluded from the count.

So I was thinking a new column with a formula similar to =COUNTIF([Error Reason]<>"No Error",1,0) would be perfect, sadly COUNTIF isn't recognised in Query Editor (I believe it uses M Language??) so what should I do?

Thanks!

Jemma

[Chihiro]

Oh, as FYI. Measure is done in DAX in data model and not in Query stage using "M".

If you want COUNTIF measure...

Try something like

CALCULATE(COUNT(Table[Column), FILTER(Table, [Column]="SomeCondition"))

Or just use appropriate visual (matrix etc) with appropriate row context, using COUNT() as measure.

[Anonymous]

Hi Chihiro,

Cool name btw. Like from the Ghibli film Spirited Away.

I wanted it to be a new column which I created in Query Editor therefore not using DAX. So a column which would hold a 1 or a 0 for each row of data based on the value in the column [error reason].

So you're right, not a measure, just a new column. Are you able to suggest the M code for an equivalent COUNTIF function?

Thanks! 🙂 

[Chihiro · Accepted solution]

If you are not aggregating, but just want 0 or 1 flag for each row.

= if [error reason] <> "No Error" then 1 else 0

If you want to count errors. Otherwise, just reverse 0 & 1.

[Chihiro]

You should use Group by function. And group data based on the column(s), using COUNT as aggregation.

If you don't want the final result to be aggregated, you can join it back to original table.