Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!!
Solved! Go to Solution.
Hi @ainabk99 ,
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.
Hi @ainabk99 ,
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.
Make sure to sort before grouping, then add the GroupKind.Local parameter to the end of your group step.
--Nate