Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I am trying to figure out how to approach finding if there is an overlap in DATETIME values. Using the table below as an example:
Run_ID 1 and 2 overlap with each other as well as Run_ID 4 and 5, but the other do not. I've determined that I belive I'll need to create an extra column so that I can SUM the overlap instances for a future measure.
Run_ID | Start_Time | End_Time | Overlaps_A_Run |
1 | 7/2/2019 9:00:00 AM | 7/2/2019 5:00:00 PM | 1 |
2 | 7/2/2019 11:00:00 AM | 7/2/2019 9:00:00 PM | 1 |
3 | 7/3/2019 2:00:00 AM | 7/3/2019 8:00:00 AM | 0 |
4 | 7/4/2019 4:00:00 PM | 7/4/2019 11:00:00 PM | 1 |
5 | 7/4/2019 1:00:00 PM | 7/4/2019 7:00:00 PM | 1 |
6 | 7/4/2019 11:30:00 PM | 7/4/2019 11:45:00 PM | 0 |
7 | 7/5/2019 9:00:00 AM | 7/5/2019 9:00:00 PM | 0 |
I've found a way to do this when dealing with the DATE only type following this tutorial: https://www.youtube.com/watch?v=SfcHsB6uWjE, but it doesn't seem to work the same for DATETIME.
Any ideas here?
Solved! Go to Solution.
is this what you want?
Column =
VAR last=maxx(FILTER('Table','Table'[End_Time]<EARLIER('Table'[End_Time])),'Table'[End_Time])
VAR next=MINX(FILTER('Table','Table'[Start_Time]>EARLIER('Table'[Start_Time])),'Table'[Start_Time])
return if(ISBLANK(last)&&next>'Table'[End_Time]||ISBLANK(next)&&last<'Table'[Start_Time],0,if(last>'Table'[Start_Time]||next<'Table'[End_Time],1,0))
Proud to be a Super User!
pls try this
Column =
var _last=MAXX(FILTER('Table','Table'[Run_ID]<EARLIER('Table'[Run_ID])&&'Table'[Overlaps_A_Run]=0),'Table'[Run_ID])
VAR _next=MINX(FILTER('Table','Table'[Run_ID]>EARLIER('Table'[Run_ID])&&'Table'[Overlaps_A_Run]=0),'Table'[Run_ID])
return if('Table'[Overlaps_A_Run]=0,0,if(ISBLANK(_last),sumx(FILTER(all('Table'),'Table'[Run_ID]<_next),'Table'[Overlaps_A_Run]),if(ISBLANK(_last),sumx(FILTER('Table','Table'[Run_ID]>_last),'Table'[Overlaps_A_Run]),sumx(FILTER('Table','Table'[Run_ID]>_last&&'Table'[Run_ID]<_next),'Table'[Overlaps_A_Run]))))
Proud to be a Super User!
Why bother split each time span to list since what's concerned about is to check whether spans of time overlap. Simple comparison between the End_Time of one run_id and the Start_Time of another run_id; that's enough.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBLCsAgDAWvIlkXNGpq7a4HELoX73+N1k+rEcHVDJMHxggIGzippVbohT+Vep+4wkip0TtA2iLo0SEuE88TU5ypTvOi0aPTXNjibHW2XxvoP91GiMll4nixT+dMk2Lilj6eK1cszT8mVjgPpQc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Run_ID = _t, Start_Time = _t, End_Time = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Run_ID", Int64.Type}, {"Start_Time", type datetime}, {"End_Time", type datetime}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Start_Time", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
Overlap = let rs = Table.ToRecords(#"Added Index") in Table.AddColumn(#"Added Index", "Overlap", each Number.From(try [End_Time] > rs{[Index]+1}[Start_Time] or [Start_Time] < rs{[Index]-1}[End_Time] otherwise 0)),
#"Removed Columns" = Table.RemoveColumns(Overlap,{"Index"})
in
#"Removed Columns"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
is this what you want?
Column =
VAR last=maxx(FILTER('Table','Table'[End_Time]<EARLIER('Table'[End_Time])),'Table'[End_Time])
VAR next=MINX(FILTER('Table','Table'[Start_Time]>EARLIER('Table'[Start_Time])),'Table'[Start_Time])
return if(ISBLANK(last)&&next>'Table'[End_Time]||ISBLANK(next)&&last<'Table'[Start_Time],0,if(last>'Table'[Start_Time]||next<'Table'[End_Time],1,0))
Proud to be a Super User!
I've been trying to think of a way to capture the number of runs that occurred in the overlap like the example below. But I'm having trouble figuring out how to add up just the occurrances during the "last" through "next" range. Is that even possible?
Run_ID | Start_Time | End_Time | Overlaps_A_Run | Runs in Overlap |
1 | 7/2/2019 9:00:00 AM | 7/2/2019 5:00:00 PM | 1 | 2 |
2 | 7/2/2019 11:00:00 AM | 7/2/2019 9:00:00 PM | 1 | 2 |
3 | 7/3/2019 2:00:00 AM | 7/3/2019 8:00:00 AM | 0 | 0 |
4 | 7/4/2019 4:00:00 PM | 7/4/2019 11:00:00 PM | 1 | 2 |
5 | 7/4/2019 1:00:00 PM | 7/4/2019 7:00:00 PM | 1 | 2 |
6 | 7/4/2019 11:30:00 PM | 7/4/2019 11:45:00 PM | 0 | 0 |
7 | 7/5/2019 9:00:00 AM | 7/5/2019 9:00:00 PM | 0 | 0 |
could you pls explain more about the your logic? why we get 2?
Proud to be a Super User!
In the example table, Run 1 and 2 are overlapping in their run timedate and I'm trying to figure out how I can record how many runs were involved in the overlap. So for this case there were 2. And the same thing for Run 4 and 5, there were 2 runs involved. Does that make sense?
pls try this
Column =
var _last=MAXX(FILTER('Table','Table'[Run_ID]<EARLIER('Table'[Run_ID])&&'Table'[Overlaps_A_Run]=0),'Table'[Run_ID])
VAR _next=MINX(FILTER('Table','Table'[Run_ID]>EARLIER('Table'[Run_ID])&&'Table'[Overlaps_A_Run]=0),'Table'[Run_ID])
return if('Table'[Overlaps_A_Run]=0,0,if(ISBLANK(_last),sumx(FILTER(all('Table'),'Table'[Run_ID]<_next),'Table'[Overlaps_A_Run]),if(ISBLANK(_last),sumx(FILTER('Table','Table'[Run_ID]>_last),'Table'[Overlaps_A_Run]),sumx(FILTER('Table','Table'[Run_ID]>_last&&'Table'[Run_ID]<_next),'Table'[Overlaps_A_Run]))))
Proud to be a Super User!
Wow. This worked perfectly. Is it too much to ask how your thought process derived this solution?
why not just sum(Overlaps_A_Run), the reslut is 4 overlap runs.
Proud to be a Super User!
I am trying to sum just the "series" of runs that overlapped each other, not the entire column. In other words, Run 1 and Run 2 are considered a series because they were involved in an overlap with each other. And in that series there were only 2. In other series there could be a lot more and I'd like to be able to capture the sum of that series.
This seems to have worked great. As somewhat of a new user to DAX and Power BI, I now need to break down your solution to better understand what each of the pieces are doing, to demystify it for myself.
You have not stated whether you need a calculated column or a measure... If you need a column, it'll be ALWAYS static. If you want a measure, then it's very easy. You have to create a table with 2 columns. One column will hold run_id and the second will hold all the time instances that are between the start_time and end_time (on the right granularity, that is). The table above will filter the new table using one-to-many, of course. The new table will be hidden. How to see if any(!!!) number of contracts have a non-empty intersection of start-end dates? Well, just see if the time instants that are being mapped to from run_id's have a non-empty intersection. Easy.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |