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.
I'm trying to display the end result of a "cobbled together" SUMPRODUCT as MM: SS but having some issues.
---Query Editor---
[Talk] column:
[Calls] column:
[sumproduct_talk] (custom column):
---Dax---
Created a Measure and placed it into a Card:
My goal is to convert this decimal (which is 5.44 minutes, or ~5min27seconds) into the MM: SS format.
I have tried the following:
Solved! Go to Solution.
Here it is unbroken if you run into issues....still looking into why FORMAT doesn't work right
test 3 = // Duration formatting // * Given a number of seconds, returns a format of "hh:mm:ss" // // We start with a duration in number of minutes VAR Duration = [A] // Minutes is the integer part VAR Minutes = INT ( [A] ) // Remaining seconds are the decimal remainder of the minutes times 60 after subtracting out the minutes VAR Seconds = ROUNDUP( ([A] - Minutes ) * 60 ,0) // We round up here to get a whole number // 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 minutes and seconds with leading zeros in the proper format "mm:ss" RETURN IF(Duration>0, CONCATENATE ( M, CONCATENATE ( ":", S ) ) , "00:00")
I'm trying to display the end result of a "cobbled together" SUMPRODUCT as MM: SS but having some issues.
---Query Editor---
[Talk] column:
[Calls] column:
[sumproduct_talk] (custom column):
---Dax---
Created a Measure and placed it into a Card:
My goal is to convert this decimal (which is 5.44 minutes, or ~5min27seconds) into the MM: SS format.
I have tried the following:
I am getting nearly the same results and it is baffling. Hopefully someone will come up with at least an explanation of why it is behaving like that (I'm certain is has something to do with how 5.44 is being stored in the PBI file).
However, if you're in a bind you can use this lengthy code snippet to format it the hard way
test 3 = // Duration formatting // * Given a number of seconds, returns a format of "hh:mm:ss" // // We start with a duration in number of seconds VAR Duration = [Measure] // 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(Duration>0, CONCATENATE ( H, CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )), "00:00:00")
Hope this helps,
David
Thanks for that mate.
My data would never has hours so I will try and remove that functionality from your snippet (and likely break everything), and see how I end up.
Here it is unbroken if you run into issues....still looking into why FORMAT doesn't work right
test 3 = // Duration formatting // * Given a number of seconds, returns a format of "hh:mm:ss" // // We start with a duration in number of minutes VAR Duration = [A] // Minutes is the integer part VAR Minutes = INT ( [A] ) // Remaining seconds are the decimal remainder of the minutes times 60 after subtracting out the minutes VAR Seconds = ROUNDUP( ([A] - Minutes ) * 60 ,0) // We round up here to get a whole number // 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 minutes and seconds with leading zeros in the proper format "mm:ss" RETURN IF(Duration>0, CONCATENATE ( M, CONCATENATE ( ":", S ) ) , "00:00")
@Anonymous- this was really bugging me so I did a full search (not just on the forum) and found this:
https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486
To my surprise it was pretty much the exact code (with comments!) I had given to you (unbeknownst to me it was not original from the person that gave it to me). So it looks like the concatenation method is the only way possible.
Kudos to @konstantinos for the code and @Greg_Deckler for the post.
The second snippet worked a treat! The only caveat is that I had to plug it into a Measure that was already divided by 60 to get me the correct time.
Thanks a lot mate!
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |