Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
thebat276
Frequent Visitor

Show 0 Instead of Blank

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 + ")

 

 

 

15-06-2022 4-32-45 PM.png15-06-2022 4-39-23 PM.png

 

 

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

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)

 

 

 

 

 

gaps.png

 

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

 

 

gaps2.png

 

Regards

 

Phil

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

4 REPLIES 4
PhilipTreacy
Super User
Super User

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)

 

 

 

 

 

gaps.png

 

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

 

 

gaps2.png

 

Regards

 

Phil

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


PhilipTreacy
Super User
Super User

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

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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

 

thebat276_0-1655339256338.png

thebat276_1-1655339267651.png

 

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

 

thebat276_3-1655339378639.png

 

 

 

Any help is appreciated. 

 

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

 

thebat276_0-1655339256338.png

thebat276_1-1655339267651.png

 

 

 

This is what I have so far ; 

 

thebat276_0-1655341773308.png

 

 

 

 

Any help is appreciated. 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.