cancel
Showing results for
Did you mean:
Post Prodigy

## Show blank cells as zeros using Sum formula in dax

Hi Experts

how would you convert the following sum formula to show '0' in empty/blank cells as opposed to blank...

OHASum = SUM(OHA_[Total])

1 ACCEPTED SOLUTION
Community Champion

well normally blanks wouldn't appear at all unless there is value in other measures
so maybe a conditional such us this would solve it:

`OHASum =VAR OHA = SUM ( OHA_[Total] )RETURN IF ( ISBLANK ( OHA ) && ( [OtherMeasure1] <> 0 || [OtherMeasure2] <> 0 ), 0, OHA )`

Thank you for the kudos 🙂

Proud to be a Super User!

7 REPLIES 7
Frequent Visitor

Could you try OHASum = IF(HASONEVALUE(SUM(OHA_[Total]));0)?

Post Prodigy

getting the following error

Community Champion

this will work

`OHASum = SUM(OHA_[Total])+0`

the issue may be that you will see more items that you would normally like

Thank you for the kudos 🙂

Proud to be a Super User!

Post Prodigy

Hi Stachu

Apologies but no working that data set in the table increase to show unwanted elements.....still not getting the desireed result

Frequent Visitor

My bad, the if-formula is not complete.

OHASum = IF(HASONEVALUE(SUM(OHA_[Total]);SUM(OHA_[Total]);0)

Post Prodigy

i have chnaged the ; to , and still getting error to many values passed to the HASONEVALUE function

Community Champion

well normally blanks wouldn't appear at all unless there is value in other measures
so maybe a conditional such us this would solve it:

`OHASum =VAR OHA = SUM ( OHA_[Total] )RETURN IF ( ISBLANK ( OHA ) && ( [OtherMeasure1] <> 0 || [OtherMeasure2] <> 0 ), 0, OHA )`

Thank you for the kudos 🙂

Proud to be a Super User!

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors