Forum Discussion
Count rows filtered by duration
it seems straightforward but I am at a loss.
I have a data set that that i have been able to create a column for duration in hours minutes and seconds 12:12:12
now I need to count the rows of how many events by
0 - 30 second
31 - 59 seconds
1 - 5 minutes
5- 10 minutes
and 10 minutes +
I tried this
but it does not capture the time in fillter properly,
any suggestions on how I filter this into these categories?
pgiorgi1 , Create a new column like
Bucket =
var _sec = hours([Time])*3600 +Minute([Time])*60 + Second([Time])
return
Switch(True(),
_sec <30 , " 0- 30 Sec",
_sec <60 , " 30- 59 Sec",
_sec<300, "<5 Mins" ,
_sec<600, "<10 Mins" ,
"More than 10 Mins"
)
Switch-Case statement of #PowerBI: https://www.youtube.com/watch?v=gelJWktlR80&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=56
3 Replies
what does 0 or 1 mean for Duration?
pgiorgi1 , Create a new column like
Bucket =
var _sec = hours([Time])*3600 +Minute([Time])*60 + Second([Time])
return
Switch(True(),
_sec <30 , " 0- 30 Sec",
_sec <60 , " 30- 59 Sec",
_sec<300, "<5 Mins" ,
_sec<600, "<10 Mins" ,
"More than 10 Mins"
)
Switch-Case statement of #PowerBI: https://www.youtube.com/watch?v=gelJWktlR80&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=56
Hi pgiorgi1,
According to your description, your question is that you need to count according to the time of each row.
This question is not complicated, but requires you to provide sample files, or illustrate how the time is calculated.
You can try:
30 seconds to 1 minute = CALCULATE( COUNTROWS('NOTES (2)') ,FILTER(ALL('NOTES (2)') ,[Duration] >= 0 && 'NOTES (2)'[Duration] <=1 ) )Best Regards,
Gallen Luo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
