Power BI Measure IF Blank Then 0

I recently worked on a Power BI report and encountered numerous Null values in the sales dataset. I used an “if blank, then 0” approach to overcome this issue.

This Power BI tutorial shows how to replace blank with zero in Power BI measure. Also, I will tell you how to replace blank with zero in the Power BI power query editor.

Power BI Measure IF Blank Then 0

I will show you how to replace blank values with zeros in the Power BI measure.

Here, I have a SharePoint list (Sales) that contains below columns with various data types:

ColumnsData Types
ProductSingle line of text
CountrySingle line of text
MRPCurrency
PRICECurrency
power bi replace blank with 0

1. Open Power BI Desktop and load the data. Then, you see data in the Data pane.

power bi calculate replace blank with 0

2. Under the Home tab, expand Visual gallery(black box) -> Click the Slicer visual.

power bi measure replace blank with 0

3. Then, add the Product into the Field using the +Add data option.

Replace Blank values with Zero on the Power BI Visualization

4. Under the Home tab, expand Visual gallery(black box) -> Click the Card visual.

Replace BLANK with Zero in Power BI Visuals

5. Then, add the MRP into the Fields using the +Add data option.

How to replace Blank with 0 in Power BI Visual

6. If you notice, when I click the Microphone in the slicer. The card visual shows (Blank). Check the screenshot below.

Power BI Replace Blanks with Zeros

To overcome this, follow the below steps:

7. Under the Home tab, click on New measure.

How to Replace BLANK with 0 (Zero) in Power BI Card

8. In the formula bar put the below expression. Then click commit.

Replace Blank with Zero = IF(SUM(Sales[MRP]) = 0,0, SUM(Sales[MRP]))

Where:

  • Replace Blank with Zero = Name of the measure
  • IF = DAX Function
  • SUM = DAX Function
  • Sales = Name of the Table
  • MRP = Name of the Column
Measure IF Blank Then 0 in Power BI

9. Under the Home tab, expand Visual gallery(black box) -> Click the Card visual.

Replace BLANK with Zero in Power BI Visuals

10. Then, add the Replace Blank with Zero into the Fields using the +Add data option.

Replace Blank with Zeros in power BI Measure

11. Now you notice when I click the Microphone in the slicer, the card visual shows 0.

Power BI Measure IF Blank Then 0

With the help of Power BI measure, you replace the blank values with zero.

Replace Blank with Zero in Power BI Power Query

I will tell you how to replace blank with zero in the Power BI power query.

I hope you loaded the above data sets.

1. Under the Home tab, click Transform data.

replace blank with zero in power bi visuals such as card

2. Select the column where the null value is presented (In my case, MRP) -> under the Home tab, click Replace Values.

replace blank values with 0 in power bi

3. Then, a Replace Values dialog box opens. Put null in the Value to Find box and 0 in the Replace With box. Then click OK.

replace blank values with null power query

4. After that, the null value is replaced with 0 in the Power BI Power Query Editor.

replace blank with null in Power BI

5. Then click Close & Apply.

How to Replace Null With 0 in Power BI Power query

6. Under the Home tab, expand Visual gallery(black box) -> Click the Card visual.

Replace BLANK with Zero in Power BI Visuals

7. Then, add the MRP into the Fields using the +Add data option.

How to replace Blank with 0 in Power BI Visual

8. Now you notice when I click the Microphone in the slicer, the card visual shows 0.

Replace Blank with Zero in Power BI Power Query

This way, you can replace blank with zero in the Power BI power query editor.

Conclusion

With the help of the if statement and power query editor, you know how to replace a blank value with zero in Power BI.

In this tutorial, we learned Power BI measures if blank then 0. Additionally, we explained how to replace blank with zero in the Power BI power query.

Moreover, you may like some more Power BI tutorials: