Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
With all credit to @konstantinos for turning ugly DAX into elegant DAX, this method turns something like a duration in seconds into a nice, readable HH:MM: SS format (space added because of smiley problems).
Duration = // Duration formatting // * @konstatinos 1/25/2016 // * Given a number of seconds, returns a format of "hh:mm:ss" // // We start with a duration in number of seconds VAR Duration = [Duration in Seconds] // 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 CONCATENATE ( H, CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) ) )
This was huge for me (and to learn about Variables in a DAX statement), but I struggled to get the blanks out for data rows which had no data - even after attribute filter was applied. This change at the end seemed to work in terms of setting it to no data (I'm not showing seconds) - but this has the behavior of null versus a blank for rows not qualified .. or this was showing up as blank - ".:"
- thanks for sharing!! And hopefully, my shorthand add helps someone else going down this road.
VAR H =
IF ( LEN ( Hours ) = 1,
CONCATENATE ( ".0", Hours ),
IF ( LEN ( Hours ) > 1,
CONCATENATE ( ".", Hours ), CONCATENATE ( "", Hours )
))
// Minutes with leading zeros
VAR M =
IF (
LEN ( Minutes ) = 1,
CONCATENATE ( ":0", Minutes ),
IF (
LEN ( Minutes ) > 1,
CONCATENATE ( ":", Minutes ), CONCATENATE ( "", Minutes )
))
// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"
RETURN
IF( Minutes > 0 , CONCATENATE (Days, CONCATENATE (H, M) ), Minutes )
User | Count |
---|---|
21 | |
19 | |
12 | |
9 | |
7 |
User | Count |
---|---|
30 | |
27 | |
14 | |
13 | |
10 |