Counting occurrences as a single occurrence if within a timeframe

[peteru9067]

Good Day,

I am creating a dashboard to show the number of trips by tag for my organization. My data is being pulled from the PI server. I am having challenges in presenting this data due to the nuisance trips that are recorded in the dataset. The nuisance trips are as a result of either starting up the unit or performing maintenance activities that requires testing of functionality, hence numerous occurances are logged of the same trip tag. 

To eliminate these nuisance trips from my dataset, I am thinking of a way to only count the tag once if it appears numerous times within a specific duration i.e. 24hrs. 

I am definitely open to any other Ideas on how I can display the message on the dashboard. Many many thanks

unit	IPL Tag	l3	Capsule Start	Capsule 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 3:58	7/29/2021 4:15	1021.786
U30	LXSHH-0002	Trips	7/29/2021 4:18	7/29/2021 4:20	152.612
U30	LXSHH-0002	Trips	7/29/2021 4:27	7/29/2021 4:28	67.7230072
U30	LXSHH-0002	Trips	7/29/2021 4:33	7/29/2021 4:34	65.6090088
U30	LXSHH-0002	Trips	7/29/2021 4:38	7/29/2021 4:38	0
U30	LXSHH-0002	Trips	7/29/2021 4:41	7/29/2021 4:41	0
U30	LXSHH-0002	Trips	7/29/2021 4:54	7/29/2021 4:55	66.9769897
U30	LXSHH-0002	Trips	7/29/2021 4:59	7/29/2021 4:59	0
U30	LXSHH-0002	Trips	7/29/2021 5:03	7/29/2021 5:03	0
U30	LXSHH-0002	Trips	7/29/2021 5:09	7/29/2021 5:09	0
U30	LXSHH-0002	Trips	7/29/2021 5:18	7/29/2021 5:20	118.018997
U30	LXSHH-0002	Trips	7/29/2021 5:23	7/29/2021 5:23	0
U30	LXSHH-0002	Trips	7/29/2021 5:57	7/29/2021 5:58	66.7720031
U30	LXSHH-0002	Trips	7/29/2021 6:14	7/29/2021 6:14	0
U30	LXSHH-0002	Trips	7/29/2021 6:43	7/29/2021 6:43	0
U30	LXSHH-0002	Trips	7/29/2021 7:20	7/29/2021 7:20	0
U30	LXSHH-0002	Trips	7/29/2021 7:28	7/29/2021 7:28	0
U30	LXSHH-0002	Trips	7/29/2021 8:05	7/29/2021 8:05	0
U30	LXSHH-0002	Trips	7/29/2021 8:32	7/29/2021 8:32	0
U30	LXSHH-0002	Trips	7/29/2021 8:56	7/29/2021 8:56	0
U30	LXSHH-0002	Trips	7/29/2021 9:00	7/29/2021 9:00	0
U30	LXSHH-0002	Trips	7/29/2021 9:06	7/29/2021 9:06	0
U30	LXSHH-0002	Trips	7/29/2021 9:15	7/29/2021 9:15	0
U30	LXSHH-0002	Trips	7/29/2021 9:23	7/29/2021 9:23	0
U30	LXSHH-0002	Trips	7/29/2021 9:39	7/29/2021 9:39	0
U30	LXSHH-0002	Trips	7/29/2021 10:55	7/29/2021 10:55	0
U30	LXSHH-0002	Trips	7/29/2021 14:02	7/29/2021 14:02	0
U30	LXSHH-0002	Trips	7/29/2021 16:21	7/29/2021 16:21	0
U30	LXSHH-0002	Trips	7/29/2021 16:46	7/29/2021 16:48	132.462997
U30	LXSHH-0002	Trips	7/29/2021 16:56	7/29/2021 16:56	0
U30	LXSHH-0002	Trips	7/29/2021 17:18	7/29/2021 17:18	0
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

[peteru9067]

Please any help on the original question on the thread

[v-easonf-msft]

Hi, @peteru9067 

Not fully sure what you want.

Can you share you expected result in excel?

Best Regards,
Community Support Team _ Eason

[peteru9067]

I have found help on this issue through another board thread "Dax".

[amitchandak]

@peteru9067 , You can measure like

Sumx(summarize(Table, [Unit], [IPL Tag], "_max", Max([Duration]), [_1])

or

Sumx(summarize(Table, [Unit], [IPL Tag], [Capsule Start], "_max", Max([Duration]), [_1])

[peteru9067]

Thanks a lot for the quick response........ what is the last value "[_1]"