Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Greg_Deckler
Super User
Super User

Duration in DAX

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 ) ) )
    )

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
1 REPLY 1
Anonymous
Not applicable

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 )

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors