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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
sjpbi
Frequent Visitor

How to convert "5 mins 5 sec" format in column into numeric form?

I have a dataset with this columns and I need to aggregate the values in the outage duration. I need to get total outage duration for a certain date(last down) and the total overall outage duration.

 

sjpbi_0-1708308700947.png

 

By this dataset, I need to aggregate this so I can have a card visual to display total outage duration (1 Days 4 Hrs 28 Mins 19 Secs)

 

2 ACCEPTED SOLUTIONS
ryan_mayu
Super User
Super User

@sjpbi 

you can do the data transform in PQ

1. replace hrs to 3600, replace mins to 60 and replace secs to 1

11.PNG

2. split the column by space

12.PNG

3. create a new column to get seconds

13.PNG

 

 

at last you can create a dax

Measure = 
VAR total=SUM('Table'[Custom])
VAR _day=INT(total/3600/24)
VAR _h=int((total-_day*3600*24)/3600)
VAR _min=INT((total-_day*3600*24-_h*3600)/60)
VAR _sec=int((total-_day*3600*24-_h*3600-_min*60))
return _day&" Days "&_h&" Hrs "&_min&" Mins "&_sec&" Secs"

 

the output is different from yours. could you pls double check your expected output?

11.PNG

 

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

How about if there are in days in the column as well like "2 days 5 Mins 5 Secs"? What is the value to replace the days text?

View solution in original post

4 REPLIES 4
Arul
Super User
Super User

@sjpbi ,

Is it possible that hours can go beyond 24 hrs for each row?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


ryan_mayu
Super User
Super User

@sjpbi 

you can do the data transform in PQ

1. replace hrs to 3600, replace mins to 60 and replace secs to 1

11.PNG

2. split the column by space

12.PNG

3. create a new column to get seconds

13.PNG

 

 

at last you can create a dax

Measure = 
VAR total=SUM('Table'[Custom])
VAR _day=INT(total/3600/24)
VAR _h=int((total-_day*3600*24)/3600)
VAR _min=INT((total-_day*3600*24-_h*3600)/60)
VAR _sec=int((total-_day*3600*24-_h*3600-_min*60))
return _day&" Days "&_h&" Hrs "&_min&" Mins "&_sec&" Secs"

 

the output is different from yours. could you pls double check your expected output?

11.PNG

 

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




How about if there are in days in the column as well like "2 days 5 Mins 5 Secs"? What is the value to replace the days text?

then change days to 86400





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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