Show 0 Instead of Blank

[thebat276]

Hello, 

I am trying to use a Visual Card with a Count and a Visual Filter to Only Display 90 Days. I can't figure out how to do it for Text. 

The Table Name is Contracts and the column name is Contract Expiry(Text) 

Contract Expiry =

VAR gap='Contracts'[Ends]-TODAY()

return SWITCH(TRUE(),gap==0,"No End Date",gap<=30,"30 Days",gap<=60,"60 Days",gap<=90,"90 Days",gap<=120,"120 Days","180 Days + ")

[PhilipTreacy · Accepted solution]

Hi @thebat276 

Download example PBIX file with code/visuals shown below

Sorry I missed that it's returning text so you can't add a number to text! 

If you could supply your file it would make things easier.  

I've used DATEDIFF and changing the 0 to "" in the RETURN statement.  

Contract Expiry = 

VAR gap = DATEDIFF([Ends],TODAY(),DAY)

VAR _result = SWITCH( TRUE(),

                      gap=0,"No End Date",

                      gap<=30,"30 Days",

                      gap<=60,"60 Days",

                      gap<=90,"90 Days",

                      gap<=120,"120 Days",

                      "180 Days + "
                )

RETURN IF( ISBLANK(_result), "", _result)

But that's just the first part, what you actually want is to display 0 rather than Blank in the card.  To do that your count of the expiry periods shoudl look somethign like this, with +0 added to the end

Expired Within 90 Days = CALCULATE(COUNTROWS('Contracts'), FILTER('Contracts', 'Contracts'[Contract Expiry] = "90 Days")) + 0

Regards

Phil

[PhilipTreacy]

Hi @thebat276 

To check for equality you only need 1 = sign so your code should have gap=0 :

Contract Expiry =

VAR gap='Contracts'[Ends]-TODAY()

return SWITCH(TRUE(),gap=0,"No End Date",gap<=30,"30 Days",gap<=60,"60 Days",gap<=90,"90 Days",gap<=120,"120 Days","180 Days + ")

To show a 0 instead of (Blank), typically you'd just add 0 to the calculation :

Contract Expiry =

VAR gap='Contracts'[Ends]-TODAY()

return SWITCH(TRUE(),gap=0,"No End Date",gap<=30,"30 Days",gap<=60,"60 Days",gap<=90,"90 Days",gap<=120,"120 Days","180 Days + ") + 0

Or you can do this

Contract Expiry =

VAR gap='Contracts'[Ends]-TODAY()

VAR _result = SWITCH( TRUE(),

                      gap=0,"No End Date",

                      gap<=30,"30 Days",

                      gap<=60,"60 Days",

                      gap<=90,"90 Days",

                      gap<=120,"120 Days",

                      "180 Days + "
                )

RETURN IF( ISBLANK(_result), 0, _result)

Regards

Phil

[thebat276]

Thank you but here is what I got with your proposed solution ; 

I also discovered I have another issue  - BLANK is coming out as 30 days when it should be " No End Date"

Any help is appreciated. 

[thebat276]

Thank you but here is what I got with your proposed solution ; 

This is what I have so far ; 

Any help is appreciated.