Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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)
Solved! Go to 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
Proud to be a 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)
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
Proud to be a 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
Proud to be a Super User!
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.
Thank you but here is what I got with your proposed solution ;
This is what I have so far ;
Any help is appreciated.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
80 | |
65 | |
52 | |
49 |
User | Count |
---|---|
212 | |
89 | |
79 | |
68 | |
60 |