Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tarun89engg
Helper I
Helper I

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.

2020-06-15_16-32-59.png

 

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. 

 

result.png

 

Powerbi File errorlogs.pibx attached 

 

Thanks.

1 ACCEPTED SOLUTION
v-eachen-msft
Community Support
Community Support

Hi @tarun89engg ,

 

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.

1-1.PNG

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

5 REPLIES 5
tarun89engg
Helper I
Helper I

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.

 

2020-06-15_12-05-02.png

 

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.

 

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.

Hi @tarun89engg ,

 

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

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

Hello @v-eachen-msft 

 

2020-06-16_8-44-06.png

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
Community Support
Community Support

Hi @tarun89engg ,

 

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.

1-1.PNG

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors