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 a...
  • 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

     

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Unfortunately that did not work.