Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi professionals, I have data like this
I need %up time, %down time, uptime hours and downtime hours
Can someone give me ideas please.
Thanks.
Solved! Go to Solution.
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
Name | DateTime | Duration | Status |
A | 1/10/2021 10:29 | 59 | Up |
A | 1/10/2021 10:30 | 478957 | Up |
A | 12/10/2021 22:45 | 3894 | Down |
A | 12/10/2021 23:50 | 145046 | Up |
B | 1/10/2021 10:30 | 37 | Up |
B | 1/10/2021 10:30 | 479099 | Up |
B | 12/10/2021 22:41 | 4195 | Down |
B | 12/10/2021 23:51 | 144955 | Up |
C | 11/10/2021 15:23 | 37 | Up |
C | 11/10/2021 15:24 | 112805 | Up |
C | 12/10/2021 22:44 | 3895 | Down |
C | 12/10/2021 23:49 | 145159 | Up |
D | 11/10/2021 15:24 | 37 | Up |
D | 11/10/2021 15:24 | 112805 | Up |
D | 12/10/2021 22:44 | 3895 | Down |
D | 12/10/2021 23:49 | 144943 | Up |
Hi, @jaipal
here goes your solution, if your Duration is in second:
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
I am glad it's working, if you could Kudo my original message, so this threat is marked as Solved.
@jaipal Many thanks
@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]] )
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
Name | DateTime | Duration | Status |
A | 1/10/2021 10:29 | 59 | Up |
A | 1/10/2021 10:30 | 478957 | Up |
A | 12/10/2021 22:45 | 3894 | Down |
A | 12/10/2021 23:50 | 145046 | Up |
B | 1/10/2021 10:30 | 37 | Up |
B | 1/10/2021 10:30 | 479099 | Up |
B | 12/10/2021 22:41 | 4195 | Down |
B | 12/10/2021 23:51 | 144955 | Up |
C | 11/10/2021 15:23 | 37 | Up |
C | 11/10/2021 15:24 | 112805 | Up |
C | 12/10/2021 22:44 | 3895 | Down |
C | 12/10/2021 23:49 | 145159 | Up |
D | 11/10/2021 15:24 | 37 | Up |
D | 11/10/2021 15:24 | 112805 | Up |
D | 12/10/2021 22:44 | 3895 | Down |
D | 12/10/2021 23:49 | 144943 | Up |
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.
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
Name | DateTime | Duration | Status |
A | 1/10/2021 10:29 | 59 | Up |
A | 1/10/2021 10:30 | 478957 | Up |
A | 12/10/2021 22:45 | 3894 | Down |
A | 12/10/2021 23:50 | 145046 | Up |
B | 1/10/2021 10:30 | 37 | Up |
B | 1/10/2021 10:30 | 479099 | Up |
B | 12/10/2021 22:41 | 4195 | Down |
B | 12/10/2021 23:51 | 144955 | Up |
C | 11/10/2021 15:23 | 37 | Up |
C | 11/10/2021 15:24 | 112805 | Up |
C | 12/10/2021 22:44 | 3895 | Down |
C | 12/10/2021 23:49 | 145159 | Up |
D | 11/10/2021 15:24 | 37 | Up |
D | 11/10/2021 15:24 | 112805 | Up |
D | 12/10/2021 22:44 | 3895 | Down |
D | 12/10/2021 23:49 | 144943 | Up |
Hi, @jaipal
here goes your solution, if your Duration is in second:
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
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?
Proud to be a Super User!
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.
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])))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |