Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kvitnitskiy
Frequent Visitor

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 ) ) ))
kvitnitskiy_0-1673373993088.png

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

 

kvitnitskiy_2-1673374050901.png

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 

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

Hi @kvitnitskiy ,

 

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

vjianbolimsft_0-1673404918901.png

 

vjianbolimsft_1-1673404918403.png

 

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.

View solution in original post

3 REPLIES 3
ppm1
Solution Sage
Solution Sage

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

Calculate and Format Durations in DAX – Hoosier BI

Pat

Microsoft Employee
v-jianboli-msft
Community Support
Community Support

Hi @kvitnitskiy ,

 

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

vjianbolimsft_0-1673404918901.png

 

vjianbolimsft_1-1673404918403.png

 

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.

amitchandak
Super User
Super User

@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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.