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

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

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

Mlekhule
Frequent Visitor

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

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

psharm10
Regular Visitor

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

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

Highlighted
Aks-1
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

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

Aks-1
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.

Aks-1
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.

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/
Aks-1
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

sirlanceohlott Helper II
Helper 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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors