Forum Discussion
find overlapping Extended event from date time
Hi all,
I am trying to find a overlapping extenteded event from below table.
| 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 |
here is the condition
if datewithaddedseconds is greater than startDate & Datewithaddedsecond is greater than earlier Datewithaddedsecond than get that datewithaadedseconds and put in new column.
so in this case for ID 1101 the Last overlapping date is from ID 1107 so the output has to be following.
I have tried following DAx but some how its gives me partial result
Last overlapping Date =
VAR start_date = ErrorLogs[Start Date]
VAR end_date = ErrorLogs[DatewithaddedSeconds]
VAR _index = ErrorLogs[Index]
RETURN
MAXX(FILTER(ErrorLogs,(ErrorLogs[DatewithaddedSeconds] >= start_date && ErrorLogs[DatewithaddedSeconds] <= end_date ) && [Index] <> _index ),[DatewithaddedSeconds])
I have integrated the same condition which i have written than why it gives me a bad result.
I am learning Powerbi so please help me to understand what i am doing wrong and how can i correct it.
the wrong reusult marked as red below.
Powerbi File errorlogs.pibx attached
Thanks.
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.
5 Replies
- AnonymousNot applicable
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.
- AnonymousNot applicable
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-msftCommunity Support
Hi Anonymous ,
I found that ID 1101 is "Conflict" in your file, which is different with your image above. Is it right?
- v-eachen-msftCommunity Support
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.