Show missing values as 0 on Matrix table

[aneedleman]

Hey all,

So in essence, my issue is that I have a matrix table for my bank showing balances and the amount of accounts for each product. The issue is that some reporting months, we had no accounts opened for certain products, so they show as blank (like in the bottom two rows of the screenshot). I would like for them to show as $0 for the balance and 0 for the count. How do I accomplish this?

It's worth noting that I've tried the "+0" method where you create a Measure to sum/count distinct and then add +0 at the end, but that has not worked for either metric. A sample data extract of how our logic works is as follows:

ReportingMonth	AccountNum	ProductType	MonthEndBalance
Jan 2025	1234-12345	Savings - Penny	$300
May 2025	1243-54123	Savings - Share Secured Credit	$25
July 2025	3123-85893	Checking - Student	$124

And so forth. What's causing the blanks is that some ReportingMonth dates don't have any accounts listed for a specific producttype, hence the blanks. Please help.

[v-tsaipranay]

Hi @aneedleman  ,

Thank you for reaching out to the Microsoft fabric community forum. Alsp thank you @MarcoSparkBI and @Greg_Deckler for your response.

Could you please confirm if the issue has been resolved. I wanted to check if you had the opportunity to review the information provided by @danextian  . Please feel free to contact us if you have any further questions.

Thank you.

[danextian · Accepted solution]

Hi @aneedleman 

Whether yo use +0 or COALESCE, the measure will not return 0 for rows that don't exist. For example, if you're the columns from your fact table and there is no such row for Jan 2025 + Savings Secured C/E in that table, you cannot assign a 0 value to that. 

The images below use the same measure but the first one uses the columns from the fact table wherein some combinations of those don't exist

The second one uses column from the different dimensions table

Please note that forcing a measure to return  zero when blank can return unexpected results and may cause a performance issue that is very noticeable on large tables.   https://data-mozart.com/why-you-should-not-replace-blanks-with-0-in-power-bi/ 

Please see the attached sample pbix.

[aneedleman]

Hi Dane,

I really appreciate your help. I don't see any attached PBIX files, could you please attach it or paste the raw DAX you used for it? Your solution seems like exactly what I'd need. Thank you!

[danextian]

added

[aneedleman]

Great, thank you this worked! Your .pbix example showed me what I needed to do differently.

For any internet lurkers that come across this, the issue with my dashboard is that the data source I was using didn't have a lookup table for our products, so there was no where that Power BI could add the 0 to. I fixed this by creating a rudimentary Product table using SELECT DISTINCT in SQL and then used Modeling to create a one-to-many relationship with my main table.

Then all I had to do was filter out the products that weren't shown in the table and it worked 🙂

[MarcoSparkBI]

hello,

you can try to use the Dax fuction "COALESCE",  this fuction has will check first argument if it is blank, if not ,it will returen the first argument, if blank, it will returen second, in your cases, you can try to modify the your dax measure . using below example, 
Sales Amount :=
COALESCE ( SUM ( Sales[Amount] ), 0 )

hoping this will give you some hint.

regards.

Marco

[Ashish_Mathur]

Hi,

Not sure how much i can help but i would like to try.  Share the download link of the PBI file.

[Greg_Deckler]

@aneedleman You can try simply adding +0 to your measures.

[aneedleman]

Hey Greg,

Thanks for the reply. Unfortunately, as stated in my original post, I've tried adding +0 to measures with no success.