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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper II

## show items with no data as 0 instead of blanks

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

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 )``

52 REPLIES 52
Frequent Visitor

One of those "**bleep** why didn't I think of this" moments just happened which is awesome.

New Member

simplesmente genial!! gratidão👏👍

Helper V

Hey @Sean  , thank you!! Simple and great.

Anonymous
Not applicable

Adding +0 or COALESCE is effecting visuals and getting error visuals exceded the available resources

Helper II

Hi!

I used the '+0' to show ) instead of blanks and it was working well. But, today I used the same thing and it now showing blank instead of 0, is there an update or a configuration in the table that I need to change? Thank you!

Anonymous
Not applicable

Thanks for the solution 🙂

Regular Visitor

Measure=0

Mind=blown

Advocate III

Sean,

This is gold my friend, saved me many IF statements 🙂

Frequent Visitor

Hi Sean,

I have  a different issue when I added the +0 at the end of my measure it still does not display 0s for Blank.

Please refer the below syntax

ITD:=
VAR MaxDate = MAX ( 'Acctng_Period'[Date] )
RETURN
if( ISBLANK( CALCULATE (
SUM(LossTriangle_POC[WINS_Medical_Paid]),
'Acctng_Period'[Date]<= MaxDate,
ALL ( 'Acctng_Period')
)) , 0 , CALCULATE (
SUM(LossTriangle_POC[WINS_Medical_Paid]),
'Acctng_Period'[Date]<= MaxDate,
ALL ( 'Acctng_Period') )) + 0

Anonymous
Not applicable

@Aks-1  it looks like you can solve this by simplifying your DAX syntax by removing the IF statement. The way your current syntax reads is: SUM these values, if the result IS BLANK then return a 0, otherwise SUM the values. In DAX, you can't really do a SUM and check it for a TRUE/FALSE unless you're using it as a VAR. To appropriate the DAX syntax, I recommend seeing if the below formula will return your desired results:

ITD:=
VAR MaxDate = MAX ( 'Acctng_Period'[Date] ) RETURN
CALCULATE (
SUM(LossTriangle_POC[WINS_Medical_Paid]), 'Acctng_Period'[Date]<= MaxDate, ALL ( 'Acctng_Period')
) + 0

There isn't a difference between your TRUE & FALSE clause calcuations, so I believe this updated syntax will correctly return the results you are looking for.

Frequent Visitor

Hi @Anonymous ,

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
Not applicable

@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

@Anonymous  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?

Resolver I

Thanks!!

Resolver I

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?

Anonymous
Not applicable

@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.

Resolver I

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!

## Helpful resources

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors