topic 0 instead of (Blank) in DAX Commands and Tips

0 instead of (Blank)

joshua1990 — Fri, 24 Jul 2020 07:43:09 GMT

Hello everybody.

I have several measures that calculate some total sums for numerous situations.

Some of them are displaying "(Blank)" if there is no value.

How can I replace this with a "0" ?

I mean, of course, I could do a pretty simple IF ...;0; ...

But is there are more efficient and elegant way?

Is there maybe a function for that?
Or from a dax performance point of view, what is the best way?

Re: 0 instead of (Blank)

Anonymous — Fri, 24 Jul 2020 08:01:24 GMT

You'll have to write out a measure for your calculation then add +0 to the end for example.

= SUM(Sales[Amt])+0

This essentially says calculate this amount then add 0.

Re: 0 instead of (Blank)

Anonymous — Fri, 24 Jul 2020 08:07:21 GMT

Hi joshua1990 ,

Try

Measure = SUM('Table'[Sales]) + 0

COALESCE Sales = COALESCE([Total Sales],0)

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

Re: 0 instead of (Blank)

Pragati11 — Fri, 24 Jul 2020 08:09:25 GMT

HI joshua1990 ,

Just modify your measure as follows: SUM(Tablename[Column]) + 0

Thanks,

Pragati

Re: 0 instead of (Blank)

mahoneypat — Fri, 24 Jul 2020 12:02:05 GMT

Multiple people have already suggested the "+0" solution to this. Depending on the columns you are using in your visual, you may see many more rows/columns show up. Visuals automatically filter out blanks, so adding 0 will return a result in those situations that would have been filtered out. You may need to add a separate filter to the visual, etc. to keep remove those unneeded ones again.

Regards,

Pat

Re: 0 instead of (Blank)

Anonymous — Fri, 24 Jul 2020 12:38:59 GMT

joshua1990 If this has solved your question. Please can you accept a solution.

Re: 0 instead of (Blank)

joshua1990 — Sat, 25 Jul 2020 07:48:59 GMT

Yes, I see. Now I see 0 values for the whole year.

I should consider that somehow 😕

Maybe a conditional format if the value is blank would solve that challenge?

Re: 0 instead of (Blank)

mahoneypat — Sun, 26 Jul 2020 21:08:56 GMT

If you are seeing more zeros than you wanted, you can use IF logic to get your desired result. For example, a date requirement or if another measure is blank or not, etc.

NewMeasure = IF(some logic, [Measure] + 0, [Measure])

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat