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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jaipal
Resolver III
Resolver III

uptime

Hi professionals, I have data like this

jaipal_0-1635485815490.png

I need %up time, %down time, uptime hours and downtime hours
Can someone give me ideas please.

 

Thanks.

3 ACCEPTED SOLUTIONS

Hello @ryan_mayu  here is the sample data. desired outcome is

1. %up time

2.%downtime

3.up time hours

4.downtime hours

 

thank you

NameDateTimeDurationStatus
A1/10/2021 10:2959Up
A1/10/2021 10:30478957Up
A12/10/2021 22:453894Down
A12/10/2021 23:50145046Up
B1/10/2021 10:3037Up
B1/10/2021 10:30479099Up
B12/10/2021 22:414195Down
B12/10/2021 23:51144955Up
C11/10/2021 15:2337Up
C11/10/2021 15:24112805Up
C12/10/2021 22:443895Down
C12/10/2021 23:49145159Up
D11/10/2021 15:2437Up
D11/10/2021 15:24112805Up
D12/10/2021 22:443895Down
D12/10/2021 23:49144943Up

View solution in original post

Hi, @jaipal 
here goes your solution, if your Duration is in second:

vojtechsima_0-1635492754965.png


Four measures:

 

Uptime = 1 - (SUMX(FILTER('Table','Table'[Status]="Down"),'Table'[Duration]) / (SUMX(FILTER('Table','Table'[Status]="Up"),'Table'[Duration])))

Downtime = SUMX(FILTER('Table','Table'[Status]="Down"),'Table'[Duration]) / (SUMX(FILTER('Table','Table'[Status]="Up"),'Table'[Duration]))

UptimeHours = (SUMX(FILTER('Table','Table'[Status]="Up"),'Table'[Duration]))/60/60

DowntimeHours = (SUMX(FILTER('Table','Table'[Status]="Down"),'Table'[Duration]))/60/60

 

View solution in original post

I am glad it's working, if you could Kudo my original message, so this threat is marked as Solved.
@jaipal Many thanks

 

View solution in original post

10 REPLIES 10
amitchandak
Super User
Super User

@jaipal , Create a new columns

 

time in hour = datediff( [Datetime], maxx(filter(Table, [Name] = earlier([Name]) && [Datetime] < earlier([Datetime]) ),[Datetime]) , minute)/60

Last Status = Var _max= maxx(filter(Table, [Name] = earlier([Name]) && [Datetime] < earlier([Datetime]) ),[Datetime])
return
maxx(filter(Table, [Name] = earlier([Name]) && [Datetime] =_max ),[status])

 

Then create measures
Uptime = sumx(filter(Table,[Last Status] ="Up"),[time in hour])

Dwontime = sumx(filter(Table,[Last Status] ="Down"),[time in hour])

0, [[Datetime]] )

@vojtechsima thank you, that works

I am glad it's working, if you could Kudo my original message, so this threat is marked as Solved.
@jaipal Many thanks

 

Hello @amitchandak Amit, thank you for your reply, I just added duration to each event, now I needed 

1. %up time

2.%downtime

3.up time hours

4.downtime hours

 

thanks in advance

NameDateTimeDurationStatus
A1/10/2021 10:2959Up
A1/10/2021 10:30478957Up
A12/10/2021 22:453894Down
A12/10/2021 23:50145046Up
B1/10/2021 10:3037Up
B1/10/2021 10:30479099Up
B12/10/2021 22:414195Down
B12/10/2021 23:51144955Up
C11/10/2021 15:2337Up
C11/10/2021 15:24112805Up
C12/10/2021 22:443895Down
C12/10/2021 23:49145159Up
D11/10/2021 15:2437Up
D11/10/2021 15:24112805Up
D12/10/2021 22:443895Down
D12/10/2021 23:49144943Up
ryan_mayu
Super User
Super User

@jaipal 

could you pls provide the data in the table not the screenshot? What's more, pls provide the expected output based on your sample data.





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

Proud to be a Super User!




Hello @ryan_mayu  here is the sample data. desired outcome is

1. %up time

2.%downtime

3.up time hours

4.downtime hours

 

thank you

NameDateTimeDurationStatus
A1/10/2021 10:2959Up
A1/10/2021 10:30478957Up
A12/10/2021 22:453894Down
A12/10/2021 23:50145046Up
B1/10/2021 10:3037Up
B1/10/2021 10:30479099Up
B12/10/2021 22:414195Down
B12/10/2021 23:51144955Up
C11/10/2021 15:2337Up
C11/10/2021 15:24112805Up
C12/10/2021 22:443895Down
C12/10/2021 23:49145159Up
D11/10/2021 15:2437Up
D11/10/2021 15:24112805Up
D12/10/2021 22:443895Down
D12/10/2021 23:49144943Up

Hi, @jaipal 
here goes your solution, if your Duration is in second:

vojtechsima_0-1635492754965.png


Four measures:

 

Uptime = 1 - (SUMX(FILTER('Table','Table'[Status]="Down"),'Table'[Duration]) / (SUMX(FILTER('Table','Table'[Status]="Up"),'Table'[Duration])))

Downtime = SUMX(FILTER('Table','Table'[Status]="Down"),'Table'[Duration]) / (SUMX(FILTER('Table','Table'[Status]="Up"),'Table'[Duration]))

UptimeHours = (SUMX(FILTER('Table','Table'[Status]="Up"),'Table'[Duration]))/60/60

DowntimeHours = (SUMX(FILTER('Table','Table'[Status]="Down"),'Table'[Duration]))/60/60

 

@jaipal 

do you want to calculate overall %up/ down time? or the percentage for each name?

how to calculate the hours?Using the duration? is the duration seconds or minutes? 





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

Proud to be a Super User!




@ryan_mayu 

I need overall %uptime and %downtime for month. and each name as well

and need to calculate duration based on duration, and yes duration is in seconds. 

vojtechsima_1-1635494286004.png

 

 

Uptime = 
var Uptime = CALCULATE(
    1 - (SUMX(FILTER('Table','Table'[Status]="Down"),'Table'[Duration]) / (SUMX(FILTER('Table','Table'[Status]="Up"),'Table'[Duration]))),
    FILTER('Table','Table'[DateTime].[MonthNo]<=MAX('Table'[DateTime].[MonthNo]))
)

var Check = IF(ISBLANK(Uptime),1, Uptime)

Return Check

Downtime = 
var Downtime = CALCULATE(
    SUMX(FILTER('Table','Table'[Status]="Down"),'Table'[Duration]) / (SUMX(FILTER('Table','Table'[Status]="Up"),'Table'[Duration])),
    FILTER('Table','Table'[DateTime].[MonthNo]<=MAX('Table'[DateTime].[MonthNo]))
)
var Check = IF(ISBLANK(Downtime),0, Downtime)

Return Check


UptimeHours = CALCULATE(
    (SUMX(FILTER('Table','Table'[Status]="Up"),'Table'[Duration])/60/60),
    FILTER('Table','Table'[DateTime].[MonthNo]<=MAX('Table'[DateTime].[MonthNo])))

DowntimeHours = CALCULATE((SUMX(FILTER('Table','Table'[Status]="Down"),'Table'[Duration]))/60/60,
FILTER('Table','Table'[DateTime].[MonthNo]<=MAX('Table'[DateTime].[MonthNo])))

@jaipal 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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