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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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