Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    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.

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
v-eachen-msft
Community Support
6 years ago

Hi Anonymous ,

 

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.