Forum Discussion
Show missing values as 0 on Matrix table
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.
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.
9 Replies
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.
aneedleman You can try simply adding +0 to your measures.
Hey Greg,
Thanks for the reply. Unfortunately, as stated in my original post, I've tried adding +0 to measures with no success.
Hi,
Not sure how much i can help but i would like to try. Share the download link of the PBI file.
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
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.
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!
added
