cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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.

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
Super User

you can do the data transform in PQ

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

2. split the column by space

3. create a new column to get seconds

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?

pls see the attachment below

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

Proud to be a Super User!

Frequent Visitor

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?

4 REPLIES 4
Super User

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!

Super User

you can do the data transform in PQ

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

2. split the column by space

3. create a new column to get seconds

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?

pls see the attachment below

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

Proud to be a Super User!

Frequent Visitor

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?

Super User

then change days to 86400

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

Proud to be a Super User!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors