Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |