cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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"
//
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
VAR H =
IF ( LEN ( Hours ) = 1,
CONCATENATE ( "0", Hours ),
CONCATENATE ( "", Hours )
)
VAR M =
IF (
LEN ( Minutes ) = 1,
CONCATENATE ( "0", Minutes ),
CONCATENATE ( "", Minutes )
)
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 ) ) )
)```

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
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 )
))
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 )

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors