How to replace Blank with 0 in Power BI Visual

Question

Above in first visual i have taken count of region, however when i select slicer for a particular region (e.g L America), others are showing Blank... I want to show value as 0 when it shows blank on visual. Blank is coming because i have selected a particular region in the slicer . 

Answer 1

Hi, Sujit

You can do this in two ways:

• In query-editor (follow the steps below)

1) In your power bi desktop, click on edit queries.

2) Select column right-clicks on it.

3) Choose to replace the value.

4) Enter "null" in find value in "replace value enter "0"

5) Close and apply

*You should see "0" wherever you have nulls in that column.

• Using​ the new calculated measure​

Create a new measure using the following code ​:

Column = IF(ISBLANK(**original column**),0,**original column**)

**original column** - your name for the column you are using.

Then change the value to the new column and it should work.

Unlock your data potential with an affordable Power BI course fee! Enroll Now

Comments

Hi Gitika,

I'm trying same way to replace blanks in Matrix but this way is not working. Could you (or anyone who can) please help if have another solution for this

I'm writing DAX as below:

Distinct IDs=

var dist_id = DISTINCTCOUNT('Table'[ID)

return

IF(ISBLANK(dist_id),"0",dist_id)

Some of the blanks are getting replaced however, some are still there.

 

---

Hey, @There,

Have you tried both the way shown above?

Answer 2

We can remove (Blank) by applying the Visual Level Filtering on the Slicer. Power BI didn't allow us to apply Visual Level Filtering earlier which was a major drawback. In June 2019, Microsoft released an update in which we can apply the Visual Level Filtering on the slicers as well.

Elevate your data analysis skills to expert levels with our in-depth Mastering Power BI Program. Gain the advanced expertise to analyze, visualize, and report data like a pro.

Answer 3

so you are using the raw source data, is it correct?

So its easy to you just make new column with this code

Column = IF(ISBLANK(**original column**),0,**original column**)

**original column** - your name for the column you are using.

Then change the value to the new column and it should work 

Comments

I used it in my dashboard and it works well as follows

I want to return by zero instead of null but within count fiels , so i create a measure as follows

measure = if(isblank(distinctcount(mycount),0,mycount)

and use the measure instead of the count column and it works well

thank you a lot

---

To the edureka.co owner, Your posts are always well-timed and relevant.

Answer 4

a quick and easy way, without any extra if then logic,  is to add a +0 on the measure.

i.e. APAC = Calculate(Countrows(someTable),Region="APAC") + 0