Q: I have a field in the pivot that shows duration on some event in seconds. How can I create calculated measure using DAX that would convert numeric result (seconds) to string in the format hh:mm:ss?
Q: I have a field in the pivot that shows duration on some event in seconds. How can I create calculated measure using DAX that would convert numeric result (seconds) to string in the format hh:mm:ss?
For example, when my field value is 635, I would like to see 10:35 (that is 10min and 35 second) as this would be much easier to understand. How can I do this with DAX?
A: Lets say we have a field FactTable[DurationInSec] that contains duration of some events. We can create new calculated measure [Duration String] that will take value of measure [DurationInSec] and will translate that value to the string format hh:mm:ss. Here is how we will calculate values for hours, minutes and seconds
- seconds: we will divide [DurationInSec] by 60 and will use a reminder. Formula would be: MOD(Calculate(SUM(FactTable[DurationInSec])),60). As we would like to have leading zero for single digit durations, we will adjust formula: RIGHT("0" & MOD(Calculate(SUM(FactTable[DurationInSec])),60),2). Here x = [Duration in Sec]
- minutes: we will divide [DurationInSec] by 60*60 and will use reminder as a resulting number of minutes. Formula for that would be: FLOOR(MOD(Calculate(SUM(FactTable[DurationInSec])),3600)/60,1). As we do not want to show minutes part in our result if value of [DurationInSec] is less than 60seconds, so we will adjust minute formula to: IF(Calculate(SUM(FactTable[DurationInSec]))<60,"", RIGHT("0"&FLOOR(MOD(Calculate(SUM(FactTable[DurationInSec])),3600)/60,1),2))
- hours: we will divide [DurationInSec] by 3600 (60*60) and will use whole integer part. Formula for that would be: FLOOR(Calculate(SUM(FactTable[DurationInSec]))/60/60,1). We will not show hours part of the string if duration is less than 1 hour, so adjusted hour formula would be: IF(Calculate(SUM(FactTable[DurationInSec]))<60*60,"",FLOOR(Calculate(SUM(FactTable[DurationInSec]))/60/60,1) & ":").
So our final DAX formula would be:
IF(Calculate(SUM(FactTable[DurationInSec]))<60*60,"",FLOOR(Calculate(SUM(FactTable[DurationInSec]))/60/60,1) & ":") & IF(Calculate(SUM(FactTable[DurationInSec]))<60,"", RIGHT("0"&FLOOR(MOD(Calculate(SUM(FactTable[DurationInSec])),3600)/60,1),2)) & ":" & RIGHT("0" & MOD(Calculate(SUM(FactTable[DurationInSec])),60),2)
Here are samples of results:
Duration in Sec | Duration String |
7 | :07 |
17 | :17 |
67 | 01:07 |
77 | 01:17 |
607 | 10:07 |
3607 | 1:00:07 |
3677 | 1:01:17 |
Note: It would be also very easy to adjust this formula to disply results like 1h 17min 27sec.