Display 0 or Null where the Card criteria does not find records

[stokidez]

Hi folks,

Is it possible to replace the value of 'Blank' in this card to either null or 0 when the condition does not find any records please?

Thanks,

[Anonymous]

1. Create a New column like 

If( ISBLANK('Breach Log'[Date Identified (GMT)]) , 0 , 'Breach Log'[Date Identified (GMT)])

2. Use this new column in your Card visual or in your calculated Measure

[stokidez]

Hi @Anonymous I get the following message - what am I doing wrong?

[Anonymous]

Hi @stokidez 

Please create a column not Measure

[Krutigawale33]

Hello @stokidez ,

Try with the measure

Measure = COALESCE(MAX('Breach Log'[Date Identified (GMT)]),0)

[stokidez]

Hi @Krutigawale33, this returns a value in the thousands as opposed to 0 which was the expected result 

[Angith_Nair]

Hi @stokidez ,

Try with this..

Measure = COALESCE ( COUNT('Breach Log'[Date Identified (GMT)]),0)

[Anonymous]

Hello,

Please try any of the following :

1. IF ( ISBLANK( [measure] ), 0 , [measure] )
2. COALESCE( [measure] , 0 )

[stokidez]

Sorry I couldn't get this to work with either formula - do you know where I'm going wrong? Thanks.

[Anonymous]

• Here, you are using column and and you cannot display it in a card.
• To display in a card, you have to use measure which gives scalar value, DAX for this is: 
  • IF ( ISBLANK( [measure] ), 0 , [measure] )
• Talking about the error in a picture, you have to close the round bracket Immediately after ISBLANK DAX. You have closed the bracket at the end of DAX which gives you error.

[stokidez]

Sorry @Anonymous I'm a real newbie here so if you can spell it out it would be appreciated.

My field is titled 'Breach Log'[Date Identified (GMT). The card has a filter which is after X date and before Y date. There are currently no records which match that criteria. How do I show null or 0 if there are no records returned?

[Anonymous]

Measure= IF(ISBLANK(SUM('Breach Log'[Date Identified (GMT)])), 0, SUM('Breach Log'[Date Identified (GMT)]))

[stokidez]

@Anonymous Thanks for the response. I get the following result 0.00. However, I expect over time that records will meet the date criteria set - I've amended my date range and it gives me a value not a count of the number of instances where that date range appears in the data-set.

If I amend the date range so it picks up records, there are actually 19 which match this date range, however the measure shows 44k.

[Anonymous · Accepted solution]

measure= IF(ISBLANK(COUNT('Breach Log'[Date Identified (GMT)])), 0, COUNT('Breach Log'[Date Identified (GMT)]))

[stokidez]

Thanks again @Anonymous - It worked! Many thanks for your perseverance!

[Jihwan_Kim]

Hi, @stokidez 

Please try to write your DAX measure in the card visual like below.

cardvisual = 

COALESCE (your measure, "")

Please let me know if it works.

[stokidez]

@Jihwan_Kim I tried your forumla, I'm obviously doing something wrong - if you could point me in the right direction that would be great.

[amitchandak]

@stokidez , +0 in measure 

example 

meausre = sum(Table[Value])+0

[stokidez]

Hiya @amitchandak , I've tried your forumula with my field but I receive the following error - what am I doing wrong please? My field is called Date Identified (GMT)