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.

 

IndexStart DateTimeDiff in SecDatewithaddedSeconds
10983/28/2020 2:04:00 AM19521084/19/2020 4:19:08 PM
10993/27/2020 1:14:00 AM20853/27/2020 1:48:45 AM
11003/26/2020 2:33:00 AM1110343/27/2020 9:23:34 AM
11013/20/2020 12:30:00 PM638673/21/2020 6:14:27 AM
11023/20/2020 12:26:00 PM473/20/2020 12:26:47 PM
11033/20/2020 12:25:00 PM423/20/2020 12:25:42 PM
11043/20/2020 12:24:00 PM203/20/2020 12:24:20 PM
11053/20/2020 12:00:00 PM11253/20/2020 12:18:45 PM
11063/20/2020 11:07:00 AM578433/21/2020 3:11:03 AM
11073/19/2020 6:46:00 AM1685633/21/2020 5:35:23 AM
11083/19/2020 6:38:00 AM603/19/2020 6:39:00 AM
11093/19/2020 8:10:00 AM59973/19/2020 9:49:57 AM
11103/18/2020 10:11:00 AM4493/18/2020 10:18:29 AM
11113/18/2020 9:53:00 AM9953/18/2020 10:09:35 AM
11123/18/2020 6:07:00 AM294733/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

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hello all,

     

    I have table called error logs with 4 columns below is the sample data on which i am working right now.

     

    IndexStart DateTimeDiff in SecDatewithaddedSeconds
    10983/28/2020 2:04:00 AM19521084/19/2020 4:19:08 PM
    10993/27/2020 1:14:00 AM20853/27/2020 1:48:45 AM
    11003/26/2020 2:33:00 AM1110343/27/2020 9:23:34 AM
    11013/20/2020 12:30:00 PM638673/21/2020 6:14:27 AM
    11023/20/2020 12:26:00 PM473/20/2020 12:26:47 PM
    11033/20/2020 12:25:00 PM423/20/2020 12:25:42 PM
    11043/20/2020 12:24:00 PM203/20/2020 12:24:20 PM
    11053/20/2020 12:00:00 PM11253/20/2020 12:18:45 PM
    11063/20/2020 11:07:00 AM578433/21/2020 3:11:03 AM
    11073/19/2020 6:46:00 AM1685633/21/2020 5:35:23 AM
    11083/19/2020 6:38:00 AM603/19/2020 6:39:00 AM
    11093/19/2020 8:10:00 AM59973/19/2020 9:49:57 AM
    11103/18/2020 10:11:00 AM4493/18/2020 10:18:29 AM
    11113/18/2020 9:53:00 AM9953/18/2020 10:09:35 AM
    11123/18/2020 6:07:00 AM294733/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's avatar
      Anonymous
      Not 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-msft's avatar
        v-eachen-msft
        Community 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-msft's avatar
    v-eachen-msft
    Community 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.