Replacing (blank) with 0

[desarrolloaudit]

I am trying to replace the value (Blank) in a number card with 0, but when performing the formula I get this error (Expressions that yield variant data-type cannot be used to define calculated columns.) How can I fix it?

The formula is: 

Column = IF(ISBLANK('Formato medidas de prevencion'[Medida de prevención implementada]),0,'Formato medidas de prevencion'[Medida de prevención implementada])

[Anand24]

Hi @desarrolloaudit ,

You can do this very easily with Power Query Editor(PQE) in following steps:

Open PQE -> Right-Click on the column name -> Replace Values -> Keep value to Find blank and type 0 in Replace with field -> OK 

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

[Anand24]

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

[desarrolloaudit]

@Anand24

It didn't work

[parry2k · Accepted solution]

@desarrolloaudit you have to simply add a measure like this and use this on each card as you are filtering each card

Count = COUNTROWS ( Table) + 0

Use above measure in each card.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@desarrolloaudit you can simply do +0

Measure = SUM ( Tabe[Amount] ) + 0

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[desarrolloaudit]

@parry2k 

What happens is that the values of that column are text, and the card only counts how many times that value appears in the column

[parry2k]

@desarrolloaudit doesn't matter if it is a count of a text column or a sum of number column, at end both returns a number and +0 will do it.

If that's not your question, provide more context with examples. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[desarrolloaudit]

@parry2k 

When I create the measure with the formula you gave me, I get this error

[parry2k]

@desarrolloaudit I don't know what is the datatype of your columns, if you are trying to sum your text column, it will not work, you need to share sample data and what you are trying to achieve. if it is a text column then use count instead of sum. I'm shooting in the dark here, 

[desarrolloaudit]

@parry2k 

I have this database with records of several people and in front it has an action that that person performed, the action column [Medida de prevención implementada] is in text format

I create these cards so that PowerBI can tell me how many records each action has using the filter so that the card selects only a specific action, along with the total sum of all the records that is in green

When I select a person who has 0 values in any action it appears on the card (Blank), I want to replace that (Blank) with the value 0