Forum Discussion
find overlapping Extended event from date time
- 6 years ago
Hi Anonymous ,
Try the following DAX:
Last overlapping Date = VAR start_date = ErrorLogs[Start Date] VAR end_date = ErrorLogs[DatewithaddedSeconds] VAR _index = ErrorLogs[Index] VAR a = MAXX ( FILTER ( ErrorLogs, ( ErrorLogs[DatewithaddedSeconds] >= start_date && ErrorLogs[DatewithaddedSeconds] <= end_date ) && [Index] <> _index ), [DatewithaddedSeconds] ) VAR b = LOOKUPVALUE ( ErrorLogs[Index], ErrorLogs[DatewithaddedSeconds], a ) RETURN IF ( b > ErrorLogs[Index], a )Here is the result.
Hello all,
I have table called error logs with 4 columns below is the sample data on which i am working right now.
| Index | Start Date | TimeDiff in Sec | DatewithaddedSeconds |
| 1098 | 3/28/2020 2:04:00 AM | 1952108 | 4/19/2020 4:19:08 PM |
| 1099 | 3/27/2020 1:14:00 AM | 2085 | 3/27/2020 1:48:45 AM |
| 1100 | 3/26/2020 2:33:00 AM | 111034 | 3/27/2020 9:23:34 AM |
| 1101 | 3/20/2020 12:30:00 PM | 63867 | 3/21/2020 6:14:27 AM |
| 1102 | 3/20/2020 12:26:00 PM | 47 | 3/20/2020 12:26:47 PM |
| 1103 | 3/20/2020 12:25:00 PM | 42 | 3/20/2020 12:25:42 PM |
| 1104 | 3/20/2020 12:24:00 PM | 20 | 3/20/2020 12:24:20 PM |
| 1105 | 3/20/2020 12:00:00 PM | 1125 | 3/20/2020 12:18:45 PM |
| 1106 | 3/20/2020 11:07:00 AM | 57843 | 3/21/2020 3:11:03 AM |
| 1107 | 3/19/2020 6:46:00 AM | 168563 | 3/21/2020 5:35:23 AM |
| 1108 | 3/19/2020 6:38:00 AM | 60 | 3/19/2020 6:39:00 AM |
| 1109 | 3/19/2020 8:10:00 AM | 5997 | 3/19/2020 9:49:57 AM |
| 1110 | 3/18/2020 10:11:00 AM | 449 | 3/18/2020 10:18:29 AM |
| 1111 | 3/18/2020 9:53:00 AM | 995 | 3/18/2020 10:09:35 AM |
| 1112 | 3/18/2020 6:07:00 AM | 29473 | 3/18/2020 2:18:13 PM |
I would like to have a new column as Measure to write a conflict and noconflict based on following condition
condition 1:
if the datewithaddedsecond is greater than the other event than its a conflict.
if the datewithaddedsecond covers the other error in that time range than its a conflict.
if the datewithaddedsecond exceeds the timerange than it is not conflict and for that row a differce after overlapping time calculate in another column.
below is the image what i would like to have in final solution.
as you can see from above picture that for ID 1110 and 1111 it already covers this time in ID 1112 that's why its conflict. same for goes for ID 1102 to 1106 all are conflict because all this time is covered by ID 11107, but the ID 1101 exceeds the time with overlapping time in Sec with 2344.
all the extending events i marked with blue and all the event which covers other event marked with Yellow and conflicts with orange for your visuilization.
I have tried following DAX Mesaure as column
check =
VAR start_date = ErrorLogs[Start Date]
VAR end_date = ErrorLogs[DatewithaddedSeconds]
VAR _index = ErrorLogs[Index]
RETURN
IF (
ISBLANK (
COUNTX (
FILTER (
ErrorLogs,
(
start_date >=ErrorLogs[Start Date]
&& start_date <= ErrorLogs[DatewithaddedSeconds]
)
&& [Index] <> _index
),
[Start Date]
)
),
"NoConflict",
"Conflict"
)
But I am not getting the results which i would like to have. there is ID 1109 which gives me a result as conflict but its not conflict with other. I have also attached pbix file for your refrence.
any help is greatly appreciated. if you need info let me know.
update on post:
I think the DAX mesaure which is written is giving me the correct result. It was me who calculated wrong in excel. for ID 1109 it conflicts with ID 1107. The date range for ID 1109 is in the range of 1107 that's the reason I am getting conflict on ID 1109. So i think it works.
But i still looking for a solution for Diff overlaping time in sec.
- v-eachen-msft6 years agoCommunity Support
Hi Anonymous ,
I found that ID 1101 is "Conflict" in your file, which is different with your image above. Is it right?
- Anonymous6 years agoNot applicable
Hello v-eachen-msft
the Mesaure which i have written gives me a correct result i have double check this. ID 1101 it is not conflict because the ID 1101 is exteneded event with ID 1107. the mesaure which i have written dont gives this event as conflict and this is what i need.
ID 1107 start with 03.19.2020 6:46:00 AM until 21.03.2020 5:35:23 and
ID 1101 Start with 03.20.2020 12:30:00 AM unitl 21.03.2020 6:41:27 so this event is extending with 2344 second in comparison with the ID 1107.
what troubles me right now is how can i calculate this extending seconds in another column.
I hope it clears now.
Thanks for giving your attention to the problem.