Forum Discussion
Show missing values as 0 on Matrix table
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
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 🙂