Forum Discussion
Helper IIICount duplicates only once if within a certain duration i.e 24hrs
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 |
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 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] )
10 Replies
smpa01Community Champion
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
peteru9067Wow..... 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
- 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