formatting time value to hh:mm:ss

just a click on a translate button and this is so helpful thanks a lot.

PS: use jbocachicasuggested website, if you want to convert from seconds to this format HH:mm:ss, I used the following expression using DAX. I just created a new measure using the existing measure in seconds, because my seconds rae in a measure not a column, well I am getting my Data from Analysis Services.

Measure :=  FORMAT(FactTable[MeasureName in seconds]/86400, "HH:mm:ss") 

By magic you have it in one single line haha.

Read about Format Function here ==> https://msdn.microsoft.com/en-us/query-bi/dax/format-function-dax

Eric_Zhang

Thank you!!! You are officially a legend =D

Worked really well to convert seconds into time format from a calculation in a measure!!!

To be honest, there is a big lack of time formatting options in Power BI, I would love to see a function that would allow us to display time as a total number of hours like in Excel that would make life so much easier.

but anyway rant is over =D thanks again!!!

Hi, this post is in spanish but will be helpful in this case.

http://blog.iwco.co/2018/03/28/formato-duracion-power-bi/

Regards

Eric_Zhang
Hello I was able to create the DAX as a calculated column not a measure when creating as a measure I get the following error:

"A single value for column duration in table dialingresults cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

Either way when I use the code provided:

Time =
RIGHT ( "0" & INT ( DialingResults[Duration]/ 3600 ), 2 )

    & ":"  

  & RIGHT (     

   "0"         

   & INT ( (DialingResults [Duration]- INT (DialingResults [Duration]/ 3600 ) * 3600 ) / 60 ), 

       2  

  )  

  & ":"  

 & RIGHT ( "0" & INT(MOD(MOD (DialingResults [Duration], 3600),60)), 2 )

It is giving me mis-calculations see attached image.

Guys I think I got it.

You need a field that is just seconds and use this code as a measure.

Time = FORMAT(INT(

IF(MOD([Seconds]|60)=60|0|MOD([Seconds]|60)) +

IF(MOD(INT([Seconds]/60)|60)=60|0|MOD(INT([Seconds]/60)|60)*100) +

INT([Seconds]/3600)*10000)| "00:00:00")

Seconds = the field that contains seconds.

I'm pretty new to DAX so it's possible that I'm doing something wrong, but your code was calculating the incorrect seconds for me and I made the following changes to get it to work:

fmtCol = 
RIGHT ( "0" & INT ( TableName[Duration]/ 3600 ), 2 )
    & ":"
    & RIGHT (
        "0"
            & INT ( (TableName[Duration]- INT (TableName[Duration]/ 3600 ) * 3600 ) / 60 ),
        2
    )
    & ":"
    & RIGHT ( "0" & INT(MOD(MOD (TableName[Duration], 3600),60)), 2 )

Eric_Zhang

Hi Eric,

Your code for Power BI solution works well... the only issue Minutes displayed out of 100 min and not as standard 60 min =o?

Any advice?

Thanks

Abduvali

Had a question about D:HH:MM:SS, hopefully this helps.

Dtime (meas) = 
VAR vDur = <<enter object as seconds>>
    RETURN INT(vDur/86400) & ":" &                       //Days
	RIGHT("0" & INT(MOD(vDur/3600,24)),2) & ":" &    //Hours 
	RIGHT("0" & INT(MOD(vDur/60,60)),2) & ":" &      //Minutes
	RIGHT("0" & INT(MOD(vDur,60)),2)                 //Seconds

Hi

I am also experiencing this, have you found any solution for this?

Thanks 

Hi Eric_Zhang

I kinda stuck with the similar error when converting the seconds to duration (HH : MM : SS) format using the following query.

Select TimeinSeconds, from_unixtime(TimeinSeconds,"HH:mm:ss") as Duration
From Tablename

When I execute the above query in Impala, I'm getting the result as expected and the same query when I use to load the data into Power BI, I'm getting this "Token Comma Expected" error. Can you help me with this, please?

This is what I am looking for.  What would the calculation be if instead of converting minutes it were hours?

Thanks you! I have just updated a little bit your code to use IT in SSAS :

=FORMAT(INT( IF(MOD(('WEBSITES KPI'[Temps_passé]/1000),60)=60,0,MOD(('WEBSITES KPI'[Temps_passé]/1000),60)) + IF(MOD(INT(('WEBSITES KPI'[Temps_passé]/1000)/60),60)=60,0,MOD(INT(('WEBSITES KPI'[Temps_passé]/1000)/60),60)*100) + INT(('WEBSITES KPI'[Temps_passé]/1000)/3600)*10000), "00:00:00")

It's working fine for me 😄 !

I have divided by 1000 because I have the duration in milisecondes.