Forum Discussion
Aggregate rows only if datetime is consecutive
Hello! I am trying to do something with PowerBI and can't find the correct way of doing it.
The idea is the following, I have a table similar to this:
Location | Start DateTime | End DateTime | Type |
| A | 10/10 01:00 | 10/10 02:00 | a |
| A | 10/10 02:00 | 10/10 03:00 | a |
| A | 10/10 03:00 | 10/10 04:00 | a |
| A | 10/10 08:00 | 10/10 09:00 | a |
| A | 10/10 10:00 | 10/10 11:00 | b |
| A | 10/10 11:00 | 10/10 12:00 | b |
| B | 10/10 01:00 | 10/10 02:00 | a |
| B | 10/10 04:00 | 10/10 05:00 | b |
| B | 10/10 05:00 | 10/10 06:00 | b |
| B | 10/10 07:00 | 10/10 08:00 | a |
All the time durations are of 1h.
What I want to achieve is to aggregate the rows where datetimes are consecutive (and location and type are the same), and get something like this:
| Location | Start DateTime | End DateTime | Type |
| A | 10/10 01:00 | 10/10 04:00 | a |
| A | 10/10 08:00 | 10/10 09:00 | a |
| A | 10/10 10:00 | 10/10 12:00 | b |
| B | 10/10 01:00 | 10/10 02:00 | a |
| B | 10/10 04:00 | 10/10 06:00 | b |
| B | 10/10 07:00 | 10/10 08:00 | a |
Any idea?
Thank you!!
Hi Anonymous ,
Please follow these steps.
1. Add a Index column
2. Create measures:
Rank = RANKX (FILTER(ALL ('Table'), [Location] = MAX ('Table'[Location]) && [Type]=MAX('Table'[Type])),CALCULATE (MAX(('Table'[Start DateTime]))),,ASC) Flag = var _next= CALCULATE(MAX('Table'[Start DateTime]),FILTER(ALL('Table'),[Location]=MAX('Table'[Location]) && [Type]=MAX('Table'[Type]) && [Rank]=MAXX('Table',[Rank])+1)) var _pre= CALCULATE(MAX('Table'[Start DateTime]),FILTER(ALL('Table'),[Location]=MAX('Table'[Location]) && [Type]=MAX('Table'[Type]) && [Rank]=MAXX('Table',[Rank])-1)) var _diff1=DATEDIFF(_pre, MAX('Table'[Start DateTime]),HOUR) var _diff2=DATEDIFF(MAX('Table'[Start DateTime]),_next,HOUR) return IF(_diff2=1 || _diff1=1 ,1,0) Start = IF([Flag]=1, MINX(FILTER(ALL('Table'),[Location]=MAX('Table'[Location]) && [Type]=MAX('Table'[Type]) &&[Flag]=1),[Start DateTime]),MAX('Table'[Start DateTime])) End = IF([Flag]=1, MAXX(FILTER(ALL('Table'),[Location]=MAX('Table'[Location]) && [Type]=MAX('Table'[Type]) &&[Flag]=1),[End DateTime]),MAX('Table'[End DateTime]))Output:
3.Then create a measure for visual-level filter to keep distinct rows:
For filter = IF(MAX('Table'[Start DateTime])=[Start],1,0)Final output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Anonymous ,
Please follow these steps.
1. Add a Index column
2. Create measures:
Rank = RANKX (FILTER(ALL ('Table'), [Location] = MAX ('Table'[Location]) && [Type]=MAX('Table'[Type])),CALCULATE (MAX(('Table'[Start DateTime]))),,ASC) Flag = var _next= CALCULATE(MAX('Table'[Start DateTime]),FILTER(ALL('Table'),[Location]=MAX('Table'[Location]) && [Type]=MAX('Table'[Type]) && [Rank]=MAXX('Table',[Rank])+1)) var _pre= CALCULATE(MAX('Table'[Start DateTime]),FILTER(ALL('Table'),[Location]=MAX('Table'[Location]) && [Type]=MAX('Table'[Type]) && [Rank]=MAXX('Table',[Rank])-1)) var _diff1=DATEDIFF(_pre, MAX('Table'[Start DateTime]),HOUR) var _diff2=DATEDIFF(MAX('Table'[Start DateTime]),_next,HOUR) return IF(_diff2=1 || _diff1=1 ,1,0) Start = IF([Flag]=1, MINX(FILTER(ALL('Table'),[Location]=MAX('Table'[Location]) && [Type]=MAX('Table'[Type]) &&[Flag]=1),[Start DateTime]),MAX('Table'[Start DateTime])) End = IF([Flag]=1, MAXX(FILTER(ALL('Table'),[Location]=MAX('Table'[Location]) && [Type]=MAX('Table'[Type]) &&[Flag]=1),[End DateTime]),MAX('Table'[End DateTime]))Output:
3.Then create a measure for visual-level filter to keep distinct rows:
For filter = IF(MAX('Table'[Start DateTime])=[Start],1,0)Final output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.