Forum Discussion

kvitnitskiy's avatar
kvitnitskiy
Frequent Visitor
3 years ago
Solved

Convert text value to long time

Hello comunity.

 I have duration value  in sec(integer),  I use DAX to convert it to time 

Movement Time =
VAR Duration ='Summary table'[Duration(sec)]
VAR Hours =INT ( Duration / 3600)
VAR Minutes =INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)
VAR Seconds =ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0)
VAR H =IF ( LEN ( Hours ) = 1,
CONCATENATE ( "0", Hours ),Hours)
VAR M =IF (LEN ( Minutes ) = 1,
CONCATENATE ( "0", Minutes ),Minutes)
VAR S =IF (LEN ( Seconds ) = 1,
CONCATENATE ( "0", Seconds ),Seconds)
RETURN
CONCATENATE (H,CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) ))

Then I try appy time format for this column and got error 

 

So this perfectly works untill my time value less or equall  to 23:59:59.

question - how can I convert duration to time like hhh:nn:mm  and apply time format as well 

Convert
time
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi kvitnitskiy ,

     

    Power BI doesn't support Duration data type for time over 24 hours.

     

     

    If you need the time format, you can consider adding an extra column to save the part beyond 24h, or use the Date\Time format to convert the part beyond 24h to days

     

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    kvitnitskiy , You can have a number like

     

    1*time(h,m,s) 

     

    That will be a fraction of a day . you can multiply with 24 to get hours

     

    24*60 to get minutes

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    Hi kvitnitskiy ,

     

    Power BI doesn't support Duration data type for time over 24 hours.

     

     

    If you need the time format, you can consider adding an extra column to save the part beyond 24h, or use the Date\Time format to convert the part beyond 24h to days

     

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.