How to count the number of 0's and blanks in a column

[Anonymous]

Hi, 

I am trying to create a measure that returns the total number of 0's and blanks in a column. I have tried this DAX:

count_ZeroOrBlank = COUNTROWS(FILTER('table1','table1'[Quantity]="0" || table1[Quantity]=""))

but PowerBI returns this error when I try to use the measure in a visualization: 

MdxScript(Model) (11, 77) Calculation error in measure 'table1'[count_ZeroOrBlank]: DAX comparison operations do not support comparing values of type Number with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

How do use VALUE or FORMAT to convert one of the values and is there a better way do create the measure? 

This is my first post, so let me know if there is anyway I can be more specific or post "better" questions in the future!

Thank you in advance.

[DataVitalizer]

Hi @Anonymous 

You can use this formula knowing that null values are also considered as 0

COUNTROWS(FILTER('Table';'Table'[Column1]=0))

Did it work ? 👌Mark it as a solution to help spreading knowledge 👉A kudos would be appreciated

[rsbin · Accepted solution]

@Anonymous ,

Please try this:

Count_ZeroOrBlank = CALCULATE( COUNTROWS( table1 ),
                        FILTER( table1, table1[Quantity] = 0 || table1[Quantity] = Blank() ))

The error message I believe is due to you putting your 0 in quotes.  This is making it text.

I assume your Quantity field is numeric.

Trust this works for you.

Regards,