Display zero instead of blank in matrix table

[calgary_raptor]

I have a situation where users enter data on a form and one of the values they selectare dependent on an asset area and a date. When displaying this information in a matrix table I want to display a value regardless if there is any data that has ever been entered for this type of scenario or not. The issue is that it is displaying something but in many cases it is displaying nothing, just a blank, and this is causing the report to look incomplete. In a few cases there are no data intersections where any value on the form would be chosen for a particular asset which likely causes the blank to begin with. What I am geting at is I don't want the report to display nothing or have it be blank, I want it to display 0. What do I need to do? There has got to be a simple answer to this question! From what I have read online this seems to be a very common problem amongst other users in the Power BI community. 

What I have read is that perhaps trying to create another table that helps that encompasses these intersections to be made is a possible solution but I'm not sure why it can recognize some data and then completely ignore it in other cases like this. This has to be a bug in Power BI! My rows in this case are set to "Show items with no data" and yet I still cannot get this to work. Again, I know many people have complained about this issue and I am hopeful that someone can help here. 

I should also indicate that I am retrieving my values with a meaure that I created. The measure is as follows:

Values MEASURE = IF(ISBLANK(SUM('Combined'[Values])), 0, SUM('Combined'[Values]))

Hopefully someone can help me and no doubt a number of other users!

Image below for reference:

I will be happy to provide more details if required in order to solve this issue!

[Anonymous]

I had a similar problem where the fact table had an incomplete list of dates, (ie from the data, there was no entry for 1/1/2019). When you summarise in a table, matrix or line graph, the data point for 1/1/2019 doesn't show up, or has no entry (not even blank).

To solve the issue, you need two things:

1. Either of the already suggested solutions (+0, ISBLANK logic) AND
2. Create a Calendar table that has every date listed, create a relationship between this table and your fact table, then use the Calendar table to drive the visualisations.

Now when DAX evaluates the measure, it specifically looks at every date, and can evaluate to 0, intead of only looking at the dates with information in the fact table. 

[v-jiascu-msft]

Hi @calgary_raptor,

What will be the result if you only use the formula below? Please share a dummy sample file.

Measure = sum('Combined'[Values])

Best Regards,

Dale

[mmace1]

Add + 0 to the end of your measure. 

[calgary_raptor]

Great suggestion! I did this but unfortunately it did nothing...

TEST = IF(ISBLANK(sum('Combined'[Values])),0,sum('Combined'[Values])) + 0

[Ashish_Mathur]

Hi,

That is strange.  The IF(),ISBLANK() should work.  Share the link from where i can download your file.

[Phil_Seamark]

Hi @calgary_raptor

WHat your probem might be is the absence of rows int your for the intersecting points of the values on the axis.  One way to solve this is to generate dummy rows in your data.  This will allow you to display 0's

[calgary_raptor]

Hi and thanks for getting back to me,

I should have stated that I am pulling this data from a SharePoint on-prem list so adding dummy rows is not something I can or want to do. Any other thoughts on what I could do?