Count duplicates only once if within a certain duration i.e 24hrs

[peteru9067]

I am trying to create a dashboard of the # of Trips in my facility by day, month, and year. It may look simple however, sometimes during startup after a plant outage, a trip may occur several times during this period before getting the plant stable and running. 

I would like to filter out these nuisance trips or count them as one if it occurs within a 24hr period. 

So in the table below what I should expect or see on my dashboard is 

LXSHH-0002 occuring 2 times (once in 7/29 and again in 8/29)

FXSHH-0012 occuring 2 times in Feb and Mar

LXSHH-0004 occuring once

unit	IPL Tag	Status	Start	End	Duration
U30	LXSHH-0002	Trips	7/29/2021 1:10	7/29/2021 1:10	0
U30	LXSHH-0002	Trips	7/29/2021 1:13	7/29/2021 3:02	6537.739
U30	LXSHH-0002	Trips	7/29/2021 3:56	7/29/2021 3:56	0
U30	LXSHH-0002	Trips	7/29/2021 4:18	7/29/2021 4:20	152.612
U30	LXSHH-0002	Trips	7/29/2021 17:21	7/29/2021 17:21	0
U05	FXSHH-0012	Trips	2/20/2021 6:08	2/20/2021 6:27	1126.646
U05	FXSHH-0012	Trips	3/9/2021 19:10	3/9/2021 19:16	383.140991
U05	FXSHH-0012	Trips	3/9/2021 19:20	3/9/2021 19:27	410.959992
U30	LXSHH-0004	Trips	4/11/2021 9:44	4/15/2021 18:44	377973.975
U30	LXSHH-0002	Trips	8/29/2021 4:33	8/29/2021 4:34	65.6090088
U30	LXSHH-0002	Trips	8/29/2021 4:38	8/29/2021 4:38	0
U30	LXSHH-0002	Trips	8/29/2021 4:41	8/29/2021 4:41	0
U30	LXSHH-0002	Trips	8/29/2021 4:54	8/29/2021 4:55	66.9769897
U30	LXSHH-0002	Trips	8/29/2021 4:59	8/29/2021 4:59	0

[smpa01 · Accepted solution]

@peteru9067  does this work for you?

Measure =
VAR _0 =
    GENERATE (
        'Table',
        DATESBETWEEN ( 'Calendar'[Date], 'Table'[Start], 'Table'[End] )
    )
VAR _1 =
    SUMMARIZE ( _0, 'Table'[IPL Tag], 'Calendar'[Date] )
VAR _3 =
    SUMMARIZE (
        NATURALINNERJOIN ( _1, 'Calendar' ),
        'Calendar'[Year],
        'Calendar'[Month],
        'Table'[IPL Tag]
    )
RETURN
    COUNTX ( _3, [IPL Tag] )

pbix is attached 

[smpa01 · Accepted solution]

@peteru9067  does this work for you?

Measure =
VAR _0 =
    GENERATE (
        'Table',
        DATESBETWEEN ( 'Calendar'[Date], 'Table'[Start], 'Table'[End] )
    )
VAR _1 =
    SUMMARIZE ( _0, 'Table'[IPL Tag], 'Calendar'[Date] )
VAR _3 =
    SUMMARIZE (
        NATURALINNERJOIN ( _1, 'Calendar' ),
        'Calendar'[Year],
        'Calendar'[Month],
        'Table'[IPL Tag]
    )
RETURN
    COUNTX ( _3, [IPL Tag] )

pbix is attached 

[peteru9067]

Hi Sorry to bother you..... the code did not exactly work after further review of my huge dataset. It counted the number of occurrences for each tag by day....... I wanted to only count the tag once if it occured within a 24hr time frame.

I am assuming it should only look at the Start and go down row by row as long as the start time is still within 24hr from the initial start time count that tag as 1. once outside the 24 hr then increment that tag by another one.

[smpa01]

@peteru9067  so you are saying that in the following case, the code should expand everything inbetween 4/11/2021 and 4/15/2021 and see if they are all consecutive dates and if yes, then should be counted as 1?

U30

LXSHH-0004

Trips

4/11/2021 9:44

4/15/2021

[peteru9067]

@smpa01 not exactly so if I have LXSHH-0002 that shows up several times on the 7/29 (within a 24hr period) count that as just 1 rather than each time it shows. and then if it occurs again say the 8/15, then it becomes count of 2

[smpa01]

@peteru9067  so LXSHH-0004 should count as 5

try this

Measure 2 = 
VAR _0 =
    GENERATE (
        'Table',
        DATESBETWEEN ( 'Calendar'[Date], 'Table'[Start], 'Table'[End] )
    )
VAR _1 =
    SUMMARIZE ( _0, 'Table'[IPL Tag], 'Calendar'[Date] )
VAR _3 =
    
        NATURALINNERJOIN ( _1, 'Calendar' )
RETURN COUNTX(_3,[IPL Tag])

[peteru9067]

how are you arriving at LXSHH-0004 as 5? it should only appear once since it is only listed once in the origial table. I think what we need to do is count the rows and if the start time is still within the 24hr window from the initial start then it should only be counted once.

For example 

Lxshh-0002 7/29 1pm

lxshh-0002 7/29 2pm

lxshh-0002 7/29 5pm

lxshh-0004 7/29 2pm

lxshh-0004 7/29 3pm

lxshh-0002 8/14 10am

lxshh-0004 10/15 1pm

lxshh-0004 11/21 10am

hence Lxshh-0002 count should only be 2 and 

lxshh-0004 should be 3

[peteru9067]

@smpa01 , so as you can see in the original table I posted LXSHH-0002 shows up many times however since there are multiple times it shows up in a 24hr duration on different dates the total count should be total of 2 (7/29 & 8/29).......

[smpa01 · Accepted solution]

@peteru9067  can you try this

Measure4 = 
VAR _0 =
    SUMMARIZE ( 'Table', 'Table'[IPL Tag], 'Table'[Start] )
VAR _1 =
    ADDCOLUMNS (
        _0,
        "rank", RANKX ( FILTER ( _0, [IPL Tag] = EARLIER ( [IPL Tag] ) ), [Start],, ASC )
    )
VAR _2 =
    FILTER (
        ADDCOLUMNS (
            _1,
            "diff",
                DATEDIFF (
                    MAXX (
                        FILTER (
                            _1,
                            [IPL Tag] = EARLIER ( [IPL Tag] )
                                && [rank]
                                    = EARLIER ( [rank] ) - 1
                        ),
                        [Start]
                    ),
                    [Start],
                    HOUR
                )
        ),
        [diff]
            == BLANK ()
                || [diff] >= 24
    )
RETURN
    COUNTX ( _2, [IPL Tag] )

[peteru9067]

Exactly.... this did the trick....... thank you so much @smpa01 ....... 

[peteru9067]

Wow..... Thank you very much..... this was exactly what I was looking for and struggling with for the past 2 days. There is a second portion to this which I will post shortly. 

Again Many many thanks sir