topic Re: Convert text value to long time in DAX Commands and Tips

Convert text value to long time

kvitnitskiy — Tue, 10 Jan 2023 18:09:19 GMT

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

Re: Convert text value to long time

amitchandak — Wed, 11 Jan 2023 02:04:22 GMT

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

Re: Convert text value to long time

v-jianboli-msft — Wed, 11 Jan 2023 02:44:45 GMT

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.

Re: Convert text value to long time

ppm1 — Wed, 11 Jan 2023 02:57:29 GMT

Please see this article for a better way to handle durations like this.

Calculate and Format Durations in DAX – Hoosier BI

Pat