Forum Discussion

bdehning's avatar
bdehning
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

Getting a 0 and not blank

I have the following measure but some rows return a blank instead of a 0 if there were no results?

 

Claim Count Open = CALCULATE(COUNT(LossRunToExcel[Claim Number]),LossRunToExcel[Claim Status] <> "Closed")

 

Is there a way to introduce "0" if there is no count for a row?

Need Help
  • samdthompson's avatar
    samdthompson
    4 years ago

    Hello, something like this should fix it:

    =CALCULATE(COUNT(LossRunToExcel[Claim Number]),LossRunToExcel[Claim Status] <> "Closed")+0

     

    cheers,

  • JasserBI's avatar
    JasserBI
    4 years ago

    Hello bdehing,

     

    Try with COALESCE formula. It should solve it by giving a "0" in case there is not data. For your case, the final formula would be:

     

    Claim Count Open = COALESCE(CALCULATE(COUNT(LossRunToExcel[Claim Number]),LossRunToExcel[Claim Status] <> "Closed"),0)

4 Replies

  • samdthompson's avatar
    samdthompson
    Icon for Memorable Member rankMemorable Member
    4 years ago

    Hello, something like this should fix it:

    =CALCULATE(COUNT(LossRunToExcel[Claim Number]),LossRunToExcel[Claim Status] <> "Closed")+0

     

    cheers,

  • JasserBI's avatar
    JasserBI
    Frequent Visitor
    4 years ago

    Hello bdehing,

     

    Try with COALESCE formula. It should solve it by giving a "0" in case there is not data. For your case, the final formula would be:

     

    Claim Count Open = COALESCE(CALCULATE(COUNT(LossRunToExcel[Claim Number]),LossRunToExcel[Claim Status] <> "Closed"),0)

    • bdehning's avatar
      bdehning
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

      Thank you.  What if I need to do the same thing for Conditional or Custom Columns, if there is no measure involved.  

      • JasserBI's avatar
        JasserBI
        Frequent Visitor
        4 years ago

        Hello bdehning,

         

        In that case there would be still a DAX expression so you could apply them too. In the case of Conditional columns, one option would be to apply the condition for all of them being " " to change to 0 value. For custom columns the same.