cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ukeasyproj
Helper II
Helper II

show items with no data as 0 instead of blanks

temp 1.PNG

 

Just a little background on the above table:

 

Project Name, Category Name, and Portfolio Name are from a table called 'Projects"

 

Actual, Committed are from a table called 'Expenses'

 

Projects is a one to many relationship with Expenses

 

In the above scenario, the project "cancer test project 5" has no related records in the Expenses table, so it is showing as blank, is there a way to show 0 instead

 

I have tried doing the following in the Projects table, but it gave unexpected results:  Actual = IF(ISBLANK(SUMX(RELATEDTABLE('Expenses'), [Actual])) = FALSE(), SUMX(RELATEDTABLE('Expenses'), [Actual]), 0)

 

 

2 ACCEPTED SOLUTIONS
Vvelarde
Community Champion
Community Champion

@ukeasyproj

 

Hi, please try with this Dax Formula in a measure:

 

ActualM =
IF (
    CALCULATE ( SUM ( Expenses[Actual] ) ) = BLANK (),
    0,
    CALCULATE ( SUM ( Expenses[Actual] ) )
)

  




Lima - Peru

View solution in original post

Sean
Community Champion
Community Champion

Even easier would be to add zero to your Measure

Then you don't need an IF statement to check if the expression returns BLANK ( )

So if your SUMX Measure works get rid of the IF statement and just add " + 0  "at the end

same with if you use any other expression (something like this for example)

Measure = CALCULATE ( SUM (table[column] ), FILTER (... ) ) + 0 

This will ensure you get a 0 when its blank!

Good Luck! Smiley Happy

 

UPDATE: March 2020

New DAX COALESCE function - returns the first argument that is not blank!

If all arguments return blank then COALESCE returns blank as well!

So if you need a zero returned and not blank and your Measures don't address the blanks on their own

Add a zero as the last argument in case all Measures return blanks!

 

COALESCE ( [Measure1], [Measure2], 0 )

 

View solution in original post

42 REPLIES 42

Hi @sphillips22 ,

                                 Thank you so much for the reponse .Let me try that.

 

 

Thanks.

Anonymous
Not applicable

Wow @Sean ! I wish i had a thought process like yours. 

Anonymous
Not applicable

Kinldy assist, im still getting a blank on mine:(

 

Here's the formula: 

No of Breakdowns completed in same month = if('Coal Lab'[Start Date].[Month]='Coal Lab'[End Date].[Month],1 ,0)
With +0:
if('Coal Lab'[Start Date].[Month]='Coal Lab'[End Date].[Month],1 ,0) +0

@Anonymous  It looks like this is a calcuate column? And then you would like to sum-up the column? If this is the case try:  IF(MONTH('Coal Lab'[Start Date]) =MONTH('Coal Lab'[End Date]),1 ,0).

 

The trick with the +0 applies to creating measures, if you'd prefer to use that method it would look more like:

NoOfBreakdownsSameMonth = CALCULATE(COUNTX('Coal Lab',[<field to count>]),MONTH('Coal Lab'[Start Date]) =MONTH('Coal Lab'[End Date)) + 0

 

Let me know if this helps and/or works.

Anonymous
Not applicable

@sphillips22  the 2nd measure calc worked. Thanks you so much.

Anonymous
Not applicable

Heyy, this works! Thanks!

For me it needs some final tweaking. I'm using a measure with a start and end date and I dont want it to show zeros before the start and after the zero, because I have 3 measures that follow up each other. See screenshot. 

 

Quantitydeal = CALCULATE (
SUM ('table'[Quantity]);
FILTER('tableinvoice';'tableinvoice'[Posting Date]>=MIN('Dealsperiod'[Startdate])&& 'tableinvoice'[Posting Date] <=MAX('table2'[Enddate])))+0
 
So where the first gray line stops, the blue begins and after the blue line the second grey line starts. I dont want it to stay zero before these date periods. Do you have a solution for my formula?
 
screenshot_deal.JPG
 

Thanks!!

Hi, When i do this to my measure it does add a 0 to the measure, however when i have my matrix it expands the rows so it repeats 0's for rows that it shouldn't show. I assume i need to relate it somehow?

@orangeatom What is your dax function? And have you a screenshot of the resulting matrix? 

 

Seems something like if the row total = 0 then return blank() for the row values, instead of zeros.

I found the solution by approaching the problem in a different view. What worked for my is to correctly keep blanks at the lowest grain of my measure which I now realize was a similar yet different problem. My solution was to create a measure with a variable and a return that correctly keeps the 0's.

 

Measure Hide Blanks =
RETURN
IF(ISBLANK(Measure1),
 IF(ISBLANK(Measure2),
  IF(ISBLANK(Measure3),
   IF(ISBLANK(Measure4),
    Measure5,Measure4
   ),Measure3)
  ,Measure2)
 ,Measure1)

 

Anonymous
Not applicable

Very elegant and saved me a bunch of time!

Smartest and best answer!!

ivank
Regular Visitor

I've had multiple connections and for some reason adding a zero made my pivot disconnect with everything else and eventually messing everything up... my logic is that when I added a fixed value which was not part of the datamodel and that's why my pivot disconnected from everything.

 

The solution (same same but different).... adding  + [var] - [var]... and the var should have some value, eg be a base value.

I have a slightly diffrerent issue... I have data where i am trying to caluculate a daily average over a range of days.  For spme of the items, they have data for every day in the range, but for others they do not... so for example, Buidling A has data for all 10 days of the period analyzed.  The simple daily average for Building A is SUM(Values)/10 days.  For Building B however, say it only has data for 5 of the days... to calculate the simple average for Buliding B over the entire 10 day range, I need to simply divide by 10 as well, but the results, after trying ~10 different methods, always divides by only the 5 days where Building B had data... how do I force the blank days to be counted as zeros?

Hi

First of all, dont use (values)/10 use DIVIDE(Values),10) or you could get a divide by zero error.

 

try:

 

 

My Measure :=
VAR Values =
    SUM ( 'table name'[Column name] )
RETURN
    COALESCE ( DIVIDE ( Values, 10 ), 0 )

 

 

This will avoid the possibility of divide by zero, which is good practice and give you a zero if there is no data

 

 

 

Do you have a copy of you DAX formula, by chance?

 

It sounds like you could do a count against the day column and use that as your denominator.

Could you not look up the first and last days in your sample and then use datediff to get your denominator?

tinkinc
Frequent Visitor

Hello, this doesnt work at all with my Power Pivot both examples. The cells stay blank which then distrupts my growth calculation 

Having to write a measure everytime you want to add a field (count, sum, etc) to a card is a poor solution.  Not only do you have to create and mange the measures which provide virtually no value, but measures are a performance drain. 

 

I know the measures wouldn't cause much of a drain, but if you have a report that is struggling for performance, the last thing you want is to be creating several extra measures.

 

Microsoft should fix this.

Wow, just add zero! Such a simple and elegant solution. Very nice! Thank you!

srhoag
Regular Visitor

Doh!   Your solution was so simplistic that I totally missed that option.   That has saved me a lot of recoding and hair pulling.   Thank you!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors