Count values without zeros and without blanks

[samoberoi]

Hi,
I have a query where i want to count all the values in a column, but without the cells where there are zeros filled in and without the ones which are blank. I tried to use the following DAX, but it doesn't work.

1. CALCULATE(
2.                            COUNTROWS(
3.                                    FILTER(
4.                                       Table,
5.                                       Table[column1] <> 0
6.                                        && NOT(ISBLANK(Table[column1])))))
   
   Can someone please help me with this to check if the DAX is wrong or is there anything else?
   
   Thanks

[samoberoi]

Thank you so much for your help. It worked. 
Thanks again.

[DataNinja777]

Hi @samoberoi ,

May I ask you what is the error message you are getting?  Is your Table[column1] in numerical data format?  I tried your formula with my data and it threw in error when applied to string data type column, but produced the desired output when the column was integar data type.  

Best regards,

[samoberoi]

Hi,
When i put in Table visual it says, "Can't display the visual". Ans yes the column is in Text format, because there are text values in it. 

I used another measure as below and it works, but the first one doesn't work.

calculate(COUNTROWS(

                                     Table),

                                FILTER(Table, CONTAINSSTRING( Table[Column1]

                                 ,"&")))

Thanks

[DataNinja777 · Accepted solution]

Hi @samoberoi ,

You answered your own question 😊.  As your column in question is in text format and boolean expression accepts the evaluation of the same data type, modifying your formula to below formula will fix the problem.  Specifically, please try using "0" instead of 0 in your formula.  

CALCULATE(
                           COUNTROWS(
                                   FILTER(
                                      Table,
                                      Table[column1] <> "0"
                                       && NOT(ISBLANK(Table[column1])))))

 Best regards,