find overlapping Extended event from date time

[Anonymous]

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.

[Anonymous]

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.

Powerbi file ErrorLogs.Pibx 

any help is greatly appreciated. if you need info let me know.

[Anonymous]

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-msft]

Hi @Anonymous ,

I found that ID 1101 is "Conflict" in your file, which is different with your image above. Is it right?

[Anonymous]

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.

[v-eachen-msft · Accepted solution]

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.