cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sphillips22 Regular Visitor
Regular Visitor

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
Frequent Visitor

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

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

psharm10 Visitor
Visitor

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

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

Aks-1 Frequent Visitor
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 Regular Visitor
Regular Visitor

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
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
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
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 Regular Visitor
Regular Visitor

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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors