Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

COUNTBLANK and Count Zeroes?

Hello,

 

I have a several columns that have text values and I want a DAX that could count how many Blanks there are AND how many zeroes there are. I already use the COUNTBLANK DAX but I would like one that also incorporates zeroes. 

 

Thanks you!

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    5 years ago

    How about something like this?

     

     

    COUNTROWS ( FILTER ( Table1, ISBLANK ( Table1[ColA] ) || Table1[ColA] = "0" ) )

     

    Or a completely different approach:

     

    CALCUALTE ( COUNT ( Table1[ColA] ), ISBLANK ( Table1[ColA] ) ) + 
CALCUALTE ( COUNT ( Table1[ColA] ), Table1[ColA] = "0" )

     

8 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    5 years ago

    How about something like this?

     

     

    COUNTROWS ( FILTER ( Table1, ISBLANK ( Table1[ColA] ) || Table1[ColA] = "0" ) )

     

    Or a completely different approach:

     

    CALCUALTE ( COUNT ( Table1[ColA] ), ISBLANK ( Table1[ColA] ) ) + 
CALCUALTE ( COUNT ( Table1[ColA] ), Table1[ColA] = "0" )

     

  • Tahreem24's avatar
    Tahreem24
    Icon for Super User rankSuper User
    5 years ago

    If you want to count zero text in your column so use the below syntax:

    Count Zero = Calculate( count(Table [ColumnName]), Filter(Table, Table[ColumnName]="0")) 

     

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    Anonymous you can get a count of zeros by following

     

    Count Zero = CALCULATE ( COUNTROWS ( Table ), Table[COlumn] = "0" )

     

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    Anonymous I will go with AlexisOlson  solution if you want one measure to count for both blank and zero values but if you want separate then add a separate measure as suggested earlier.

     

    Tahreem24 should avoid using the FILTER function, it is an iterator and can have a performance impact on large tables. Just 2 cents on your solution.

     

    Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • Tahreem24's avatar
      Tahreem24
      Icon for Super User rankSuper User
      5 years ago

      Thanks parry2k  for the heads up. But sometime users say Calculate(Count(Column),Columname="0"))  doesn't work well and solution would be ended up by adding Filter. I agree that it may hamper the performance but in some cases data coming from different table so has to use filter. 

       

       

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    Tahreem24 not sure why, FILTER function is the last thing you want to use. Anyhow, let's talk about it separately, don't want to divert the topic into something totally different. 👍

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thank you for all your replies, I like this formula best:

    COUNTROWS ( FILTER ( Table1, ISBLANK ( Table1[ColA] ) || Table1[ColA] = "0" ) )