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.
Hi everyone,
I'm working on a report in Power BI that I did not build myself, and so it is quite difficult for me to understand some of the DAX.
I have a measure that already exists that shows me the Average Handling Time. However, the result of the measure is of the type text and I need it to be a decimal number since I want to be able to apply conditional formatting.
This is the DAX:
AVG handling time = VAR Duration = DIVIDE(SUM('Daily Agent'[holdtime]) + SUM('Daily Agent'[i_acwtime]) + SUM('Daily Agent'[i_acdtime]); SUM('Daily Agent'[acdcalls]); 0) // There are 3,600 seconds in an hour VAR Hours = INT ( Duration / 3600) // There are 60 seconds in a minute VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 );3600 ) / 60) // Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 );3600 ); 60 );0) // We round up here to get a whole number // These intermediate variables ensure that we have leading zero's concatenated onto single digits // Hours with leading zeros VAR H = IF ( LEN ( Hours ) = 1; CONCATENATE ( "0"; Hours ); CONCATENATE ( ""; Hours ) ) // Minutes with leading zeros VAR M = IF ( LEN ( Minutes ) = 1; CONCATENATE ( "0"; Minutes ); CONCATENATE ( ""; Minutes ) ) // Seconds with leading zeros VAR S = IF ( LEN ( Seconds ) = 1; CONCATENATE ( "0"; Seconds ); CONCATENATE ( ""; Seconds ) ) // Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss" RETURN( IF(H = "" && M = "" && S = ""; ""; CONCATENATE ( M; CONCATENATE ( ":"; S ) ) ) )
Is there a way to change this code so the output will be a decimal number instead of text?
I would only need the result in minutes, so the hours & seconds step might not be necessary.
Thanks in advance!
Solved! Go to Solution.
Hi
well, this is not a DAX issue but a mathematical one. 13:50 does NOT convert to 13.5 in decimal.
To convernt the 50 seconds in fraction of a unit you have to make a proportion x=50*1/60 so your 13:50 is actually, in decimal, 13.83
AVG handling time = VAR Duration = DIVIDE(SUM('Daily Agent'[holdtime]) + SUM('Daily Agent'[i_acwtime]) + SUM('Daily Agent'[i_acdtime]); SUM('Daily Agent'[acdcalls]); 0) // There are 3,600 seconds in an hour VAR Hours = INT ( Duration / 3600) // There are 60 seconds in a minute VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 );3600 ) / 60) // Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 );3600 ); 60 );0) // We round up here to get a whole number // These intermediate variables ensure that we have leading zero's concatenated onto single digits // Hours with leading zeros VAR H = IF ( LEN ( Hours ) = 1; CONCATENATE ( "0"; Hours ); CONCATENATE ( ""; Hours ) ) // Minutes with leading zeros VAR M = IF ( LEN ( Minutes ) = 1; CONCATENATE ( "0"; Minutes ); CONCATENATE ( ""; Minutes ) ) // Seconds with leading zeros VAR S = IF ( LEN ( Seconds ) = 1; CONCATENATE ( "0"; Seconds ); CONCATENATE ( ""; Seconds ) ) // Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss" RETURN( Minutes
You just have to change the return value
I've tried adjusting it to just return minutes but it will really only give me the minutes, not the seconds.
If I return the seconds it will show fifty.
I cannot concatenate these two together because of it being text value then.
How can I solve this?
you said you want to return MINUTES, and the measure returns minutes.
Exactly what is you desired result for like 13:50? 13? 50? 13.9 (so the 50 seconds normalized to 100)?
The ideal situation would be that it returns 13,5 @Anonymous. However, I talked about it with the guy who made the rapport and he said it might be better to start from scratch with some dummy data.
Right now I made the DAX formula return the time as 00:13:50 so if I could format that to just say 13,5 that would also be a solution.
Hi
well, this is not a DAX issue but a mathematical one. 13:50 does NOT convert to 13.5 in decimal.
To convernt the 50 seconds in fraction of a unit you have to make a proportion x=50*1/60 so your 13:50 is actually, in decimal, 13.83
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |