Replace blank with 0 on card when field has no/empty data

[bencurt4]

Hi

I have done a lot of looking online and through these forums and cannot seem to find an answer to my problem.

I am trying to replace (Blank) with a 0 on a couple of cards. These card visuals do not link to slicers or other visuals but directly to a field in the table. The field in the table is empty.

I have tried to add a measure using ## = SUM(Table[Field])+0 but when I drag this onto the field of the card I get a cross on the card.

Surely this cannot be complicated - I am fairly new to PowerBI so any help would be appreciated.

Thanks

Ben

[v-yanjiang-msft]

Hi @bencurt4 ,

Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ kalyj

[bencurt4]

Hi

I am getting this error:

Does this mean I have to use something other than SUM to display 0 instead of (Blank) ?

[Anonymous]

Hi 

You can convert the string value in the measure and then add it

Measure = SUMX('Table',CONVERT('Table'[Value 2],INTEGER))+0

[bencurt4]

Hi

This works for showing 0, however when there is data in the field I now get this:

Thanks
Ben

[v-yanjiang-msft]

Hi @bencurt4 ,

According to your description, I reproduce your problem. It's not because of the formula, but because the card visual can't display such a large number. 

If you create a table with a single row, enter a large value and put the column in a card visual, it can get the same result.

             

You can change the card visual to a table visual, it works fine.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi

I guess the column is of type string. Change that to whole number cause SUM() uses only numerical columns

[bencurt4]

Is there a different formula I could use other than SUM to show a 0 value?

[Anonymous]

Hi,

I created a sample table with a column having blank values

I created the same measure you have mentioned and i am getting output as follows

the measure is working fine for me .

if this post helps, accept it as a solution