cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Partisan
Post Partisan

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

Re: COUNT IF Measure in Query Editor

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.

View solution in original post

4 REPLIES 4
Highlighted
Solution Sage
Solution Sage

Re: COUNT IF Measure in Query Editor

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.

Highlighted
Solution Sage
Solution Sage

Re: COUNT IF Measure in Query Editor

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.

Highlighted
Post Partisan
Post Partisan

Re: COUNT IF Measure in Query Editor

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! 🙂 

Highlighted
Solution Sage
Solution Sage

Re: COUNT IF Measure in Query Editor

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.

View solution in original post

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors