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
I need to create a bucket system for each 1.2 Hours.
I have Multiple rows with different times with same unique ID.
I already created time slots
but lets consider a use case
I have a set of ID's below, I need the following result under Bucket column
ID | Time | Bucket |
231 | 10/08/2023 20:00:00 | 7 |
231 | 10/08/2023 22:00:00 | 8 |
231 | 10/08/2023 23:59:00 | 9 |
231 | 11/08/2023 02:00:00 | 10 |
233 | 12/08/2023 22:00:00 | 8 |
233 | 12/08/2023 23:59:00 | 9 |
233 | 13/08/2023 02:00:00 | 10 |
So while bucketing it should consider the Date as well so that the bucketing is in a sequence
I am using below Code to bucket
BucketColumn =
SWITCH(
TRUE(),
Task[Completed_Time] <= TIME(1, 12, 0), 1,
Task[Completed_Time] <= TIME(2, 24, 0), 2,
Task[Completed_Time] <= TIME(3, 36, 0), 3,
Task[Completed_Time] <= TIME(4, 48, 0), 4,
Task[Completed_Time] <= TIME(6, 0, 0), 5,
Task[Completed_Time] <= TIME(7, 12, 0), 6,
Task[Completed_Time] <= TIME(8, 24, 0), 7,
Task[Completed_Time] <= TIME(9, 36, 0), 8,
Task[Completed_Time] <= TIME(10, 48, 0), 9,
Task[Completed_Time] <= TIME(12, 00, 0), 10,
Task[Completed_Time] <= TIME(13, 12, 0), 1,
Task[Completed_Time] <= TIME(14, 24, 0), 2,
Task[Completed_Time] <= TIME(15, 36, 0), 3,
Task[Completed_Time] <= TIME(16, 48, 0), 4,
Task[Completed_Time] <= TIME(18, 0, 0), 5,
Task[Completed_Time] <= TIME(19, 12, 0), 6,
Task[Completed_Time] <= TIME(20, 24, 0), 7,
Task[Completed_Time] <= TIME(21, 36, 0), 8,
Task[Completed_Time] <= TIME(22, 48, 0), 9,
Task[Completed_Time] <= TIME(23, 59, 59), 10,
TRUE(), 11
)
But the thing is the entries made after 00:00:00 is bucketed under 1, rather it should be bucketed last as it is finished the next day
Appreciate your help in solving this
Thank you
Solved! Go to Solution.
HI @jeffrock,
You can try to use following calculated column formula to setting segments based on specific interval:
Bucket Group =
VAR currTime =
TIMEVALUE ( 'Table'[Time] )
VAR totalSecond =
HOUR ( currTime ) * 3600
+ MINUTE ( currTime ) * 60
+ SECOND ( currTime )
VAR interval = 72 * 60
VAR segmentation =
INT ( totalSecond / interval )
VAR offset =
IF ( MOD ( totalSecond - segmentation * interval, interval ) > 0, 1, 0 )
RETURN
IFERROR ( MOD ( segmentation, 10 ) + offset, 11 )
Regards,
Xiaoxin Sheng
HI @jeffrock,
You can try to use following calculated column formula to setting segments based on specific interval:
Bucket Group =
VAR currTime =
TIMEVALUE ( 'Table'[Time] )
VAR totalSecond =
HOUR ( currTime ) * 3600
+ MINUTE ( currTime ) * 60
+ SECOND ( currTime )
VAR interval = 72 * 60
VAR segmentation =
INT ( totalSecond / interval )
VAR offset =
IF ( MOD ( totalSecond - segmentation * interval, interval ) > 0, 1, 0 )
RETURN
IFERROR ( MOD ( segmentation, 10 ) + offset, 11 )
Regards,
Xiaoxin Sheng
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 |
---|---|
15 | |
2 | |
1 | |
1 | |
1 |
User | Count |
---|---|
24 | |
3 | |
2 | |
2 | |
2 |