Forum Discussion

peterg0417's avatar
peterg0417
Icon for Helper III rankHelper III
2 years ago
Solved

Using Dynamic Formatting to convert seconds to hours, minutes and seconds

I wrote a format string using ChatGPT but it's not working 100% correctly in certain situations. Below is the code I'm using for dynamic formatting:   VAR TotalSeconds = [Latest Execution Time (hh...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, peterg0417 

    Based on your information, I create a sample table:

     

    Then create a calculated column, try the following DAX:

    Column = 
VAR TotalSeconds = [Latest Execution Time(ss)]
VAR Hours = INT(TotalSeconds / 3600)
VAR Minutes = INT(MOD(TotalSeconds, 3600) / 60)
VAR Seconds = INT(MOD(TotalSeconds, 60))
VAR HoursText = IF(Hours > 0, Hours & "h ", "")
VAR MinutesText = IF(Minutes > 0, Minutes & "m ", "")
VAR SecondsText = IF(Seconds > 0, Seconds & "s", "")
RETURN
 IF(TotalSeconds = 0, "0", TRIM(HoursText & MinutesText & SecondsText))

    Here is my preview:

     

    How to Get Your Question Answered Quickly

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Selva-Salimi's avatar
Selva-Salimi
Icon for Solution Sage rankSolution Sage
2 years ago

Hi peterg0417 

 

Did you have a column that shows the total second? if not craete a column as follows:

duration second = hour('Table'[Duration])*3600+MINUTE('Table'[Duration])*60 + SECOND(('Table'[Duration])
 
and then write a measure as follows:
VAR _sum = SUM('Table'[duration second])

VAR _hour = RIGHT("0" & ROUNDDOWN(_sum / 3600, 0), 2)
VAR _minute = RIGHT("0" & ROUNDDOWN(MOD(_sum, 3600) / 60, 0), 2)
VAR _second = RIGHT("0" & MOD(_sum, 60), 2)

RETURN
_hour & ":" & _minute & ":" & _second

 

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly. 

peterg0417's avatar
peterg0417
Icon for Helper III rankHelper III
2 years ago

Yes. I already have a column that shows duration in seconds.

 

I also have a measure, Latest Execution Time, returns the latest duration (that's the requirement, in case there's more than 1 run per model on a given day.)

 

I need to Latest Execution Time from seconds to minutes and seconds while still keeping it as an integer, therefore I need to do it using dynamic formatting.

  • Selva-Salimi's avatar
    Selva-Salimi
    Icon for Solution Sage rankSolution Sage
    2 years ago

    then you can use your measure instead of _sum variable