cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

Re: show items with no data as 0 instead of blanks

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

Highlighted
Anonymous
Not applicable

Re: show items with no data as 0 instead of blanks

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

Highlighted
Anonymous
Not applicable

Re: show items with no data as 0 instead of blanks

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

Highlighted
Frequent Visitor

Re: show items with no data as 0 instead of blanks

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

Highlighted
Advocate II
Advocate II

Re: show items with no data as 0 instead of blanks

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

Highlighted
Frequent Visitor

Re: show items with no data as 0 instead of blanks

Hi @sphillips22 ,

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

 

 

Thanks.

Highlighted
Frequent Visitor

Re: show items with no data as 0 instead of blanks

Hi @sphillips22 ,

                               Thank you so much for the response .It worked! . Although I have one question I am also looking at the items that are not present in the database ,I have a situation where there is calculated measure which falls in a specific quadrant (period) but since the data is not there in database ,It displays as Blank but I want to show or display it has 0.00.

 

What I am trying to say is when I export it into excel that period or record is not present in the database.

 

Please refer the screenshot:

 

error4.PNG

 

 

 

Any help is highly appreciated!

 

Thanks.

Highlighted
Super User IV
Super User IV

Re: show items with no data as 0 instead of blanks

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Frequent Visitor

Re: show items with no data as 0 instead of blanks

Hi @Ashish_Mathur ,

                                   Please find an example that I have below which shows blank values in cells 9,12,30 & 33 ect as the data for these quarter periods there is no data or no record  is present in database ,the requirement is to fill these cells with 0.00 instead of keeping it as blank.

 

 

Table.PNG

 

Thanks

Highlighted
Advocate II
Advocate II

Re: show items with no data as 0 instead of blanks

Sean,

 

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

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors