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

[peterg0417]

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 (hhmmss)]

VAR Hours = INT(TotalSeconds / 3600)

VAR Minutes = INT(MOD(TotalSeconds, 3600) / 60)

VAR Seconds = INT(MOD(TotalSeconds, 60))  -- Round seconds to remove extra digits

VAR HoursText = IF(Hours > 0, Hours & "h ", "")

VAR MinutesText = IF(Minutes > 0, Minutes & "m ", "")

VAR SecondsText = IF(Seconds > 0, Seconds & "s", "")

RETURN

    TRIM(HoursText & MinutesText & SecondsText)

And this is what it returns: (notice the one line marked with an x)

Does anyone have any ideas on how to fix this? I want to use dynamic formatting so that I can utilize the bars in the cell, so the actual measure needs to stay as an integer instead of text (like the "TimeFormattedMeasure" to the left.)

Edit I want to note that depending on the day selected, different model refresh times display the value incorrectly. My refresh times from yesterday are like this:

[Anonymous · Accepted solution]

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.

[shafiz_p]

hI @peterg0417 Considering you want to convert total seconds to hour, minutes, and seconds. Try below Codes:

Hope this helps!!
If this solved your problem, please accept it as a soltuion!!


Best Regards,
Shahariar Hafiz

[peterg0417]

I tried putting your code in as dynamic formatting but the error is still there. It looks like it's entering 2  digits (74 and 13, for the 2 lines) in between the correct value for seconds.

[shafiz_p]

If your time formatted measure is correctly evaluting then use that measure. Since don't know much about your data, it is hard to tell what exactly happening. If you have total seconds, then the provided measure should evalutes correctly. 

[Selva-Salimi]

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]

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]

then you can use your measure instead of _sum variable