Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
calgary_raptor
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
Anonymous
Not applicable

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
Employee
Employee

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.
mmace1
Impactful Individual
Impactful Individual

Add + 0 to the end of your measure. 

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

 

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

 

test 2.JPG

 

 

 

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Phil_Seamark
Employee
Employee

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!

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?

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.