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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
thebat276
Regular 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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors