Forum Discussion
COUNT IF Measure in Query Editor
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
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.
4 Replies
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.
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.
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! :-)
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.