cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
calgary_raptor Frequent Visitor
Frequent Visitor

Display zero instead of blank in matrix table

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:

 

test.JPG

 

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

7 REPLIES 7
Phil_Seamark Super Contributor
Super Contributor

Re: Display zero instead of blank in matrix table

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

mmace1 Member
Member

Re: Display zero instead of blank in matrix table

Add + 0 to the end of your measure. 

calgary_raptor Frequent Visitor
Frequent Visitor

Re: Display zero instead of blank in matrix table

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?

calgary_raptor Frequent Visitor
Frequent Visitor

Re: Display zero instead of blank in matrix table

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

 

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

 

test 2.JPG

 

 

 

Super User
Super User

Re: Display zero instead of blank in matrix table

Hi,

 

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

v-jiascu-msft Super Contributor
Super Contributor

Re: Display zero instead of blank in matrix table

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DPalmer Visitor
Visitor

Re: Display zero instead of blank in matrix table

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. 

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 111 members 1,200 guests
Please welcome our newest community members: