How to Display Zero instead of Blank in Matrix Table when No Value Exists?

[DMB90]

Hello, I have tried adding "0" to measures and the COALESCE function, but nothing is working. I need to be able to show zero even when there was no data for certain items. Is there a way to fix this?

I have a simple Hours column (00.00 format of numbers) that I'm using in numerical value, but because some of the categories have no data, it's just showing as blank. I need these to be "0" instead.

[DMB90]

Thanks for providing some options everyone, but ultimately none of them worked for me. I appreciate the help.

[v-hjannapu]

Hi @DMB90,

Thanks for the update. If none of the suggestions worked, then the issue is likely with how the model is structured rather than the DAX itself. In Power BI, if a particular category combination does not exist in the fact table, the measure will not evaluate for that combination, so it will stay blank even if we use COALESCE or ISBLANK logic. Please check whether the fields used in the Matrix are coming from proper dimension tables and not directly from the fact table, and also make sure “Show items with no data” is turned on. It would really help if you can share a small sample dataset and a screenshot of the model view so we can understand the exact setup and guide you correctly.

Regards,
Community Support Team.

[cengizhanarslan]

Step 1) Enable “Show items with no data”

If a category has no records, Power BI won’t evaluate the measure → result stays blank regardless of DAX.

• In the Matrix → Rows (or Columns) field dropdown

• Enable Show items with no data

 

Step 2) Return 0 when the measure is blank

Then update your measure:

Hours =
COALESCE( SUM('Table'[Hours]), 0 )

However, if you use the fact table column directly, Power BI cannot show categories that don’t exist in data. Make sure categories come from a dimension table

[Praful_Potphode]

Hi @DMB90 ,

You can handle it in your measure.

Assuming you have a measure below:

Total Hours=SUM(Orders[Hours])

above measure will not handle blanks.to handle blanks i can modify measure to below:

Total Hours version 1=
var result=SUM(Orders[Hours])
RETURN
      IF(ISBLANK(result),0,result)

If above doesn't work,please share sample input/output. 

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[danextian]

Hi @DMB90 

It looks like you’re using columns from the fact table and trying to assign a value to a combination that doesn’t actually exist. For example, if Category A and Item 1 never appear together in the fact table, there’s no row representing that combination, so you can’t just assign a value to it.

You'll need to use a dedicated dimensions table instead

In the image below, the first table uses columns from the fact table while the second uses the columns from the dimension tables.

Be careful with forcing zeroes instead of blanks. It’s generally not a good practice because it can make results misleading. For example, if the data is filtered to June, other months may still appear in the visual but show zero values, even though they shouldn’t be there at all.

Please refer to this video https://youtu.be/C_ys3y9ftHc

[Natarajan_M · Accepted solution]

Hi @DMB90 , 

Could you please share some actual data ? I created a simple data mocking replicating your scenario , 

Created a measure by taking the sum of hour and checking if its blank and replacing it will 0 , its producing the expected result

Also please take a look at the impact of replacing blanks with zeros  
https://data-mozart.com/why-you-should-not-replace-blanks-with-0-in-power-bi/

Thanks
If this response was helpful in any way, I’d gladly accept a kudo.
Please mark it as the correct solution. It helps other community members find their way faster