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
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
95 | |
86 | |
78 | |
66 |
User | Count |
---|---|
153 | |
125 | |
114 | |
111 | |
95 |