Showing results for 
Search instead for 
Did you mean: 
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)



Community Champion
Community Champion



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


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


Lima - Peru

View solution in original post

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


Oh wow, what an elegant solution. Wish I'd searched and found this first, before trying to figure out myself. Thanks!!

Helpful resources

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!


Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors