Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

formatting time value to hh:mm:ss

I hope I am posting this to the right location as I am developing a new report.  Anyway, this is coming out of our telephone DB system and one column represented is Duration, which is an int column and represents the call in the number of seconds.  I would like to convert and format that to HH:MM:SS.  Would something like this work, or is there a better way?

 

New Duration = FORMAT(TableName[Duration]/60, "HH:mm:ss")

 

Thanks

  • Eric_Zhang's avatar
    Eric_Zhang
    9 years ago

    Anonymous

     

    Try a DAX as below.

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

    Or deal with the format in query.

    select Duration, convert(varchar(10),DATEADD(second,Duration,0),108) fmtSecs from t1

     

21 Replies

  • jbocachica's avatar
    jbocachica
    Resolver II
    9 years ago

    Hi, you are just returning the minutes, you must return the entire number.

     

    New Duration = format(((TableName[Duration] / 60)/60)/24, "HH:mm:ss")

     

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      The column value from SQL is an integer in the number of seconds so wouldnt / 60 return how many minutes, seconds, etc (except now I know what you are saying whereas if the number of minutes is more than 60).  However, I get an error when trying to use FORMAT for the column in that FORMAT cannot be used with a calculated column. 

       

       

       

       

      I also found this article: http://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486

      • jbocachica's avatar
        jbocachica
        Resolver II
        9 years ago

        Hi, you must go to Options and then find the DirectQuery options and enable the checkbox that says "Allow unrestricted measures in DirectQuery mode" and try again.

         

        Regards