Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |