cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bdehning
Resolver III
Resolver III

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?

2 ACCEPTED SOLUTIONS
samdthompson
Super User
Super User

Hello, something like this should fix it:

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

 

cheers,

// if this is a solution please mark as such. Kudos always appreciated.

View solution in original post

JasserBI
Frequent Visitor

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)

View solution in original post

4 REPLIES 4
JasserBI
Frequent Visitor

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)

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

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.

samdthompson
Super User
Super User

Hello, something like this should fix it:

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

 

cheers,

// if this is a solution please mark as such. Kudos always appreciated.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.