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
Icon for Resolver II rankResolver 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.

  • jbocachica's avatar
    jbocachica
    Icon for Resolver II rankResolver II
    9 years ago
    Uhhh... which kind of datasource are you using?
    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Straight up SQL Server data table.  Nothing special at all.

      • Eric_Zhang's avatar
        Eric_Zhang
        Icon for Microsoft Employee rankMicrosoft Employee
        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