Remove a entry in PowerBi based on next entry

[lotus22]

In picture we have a machine RW100 Side A where fail entry created by Operator 10314. The operator was able to fix the late entry with in 5 mins as we see next entry 159. How do I remove the late entry from PowerBI or exclude it when the new entry shows up within 5 mins?

10314 is Operator ID. Minutes is (minutes between created and now) which do not mean anything here.

[TheoC]

@lotus22 I am pretty sure that's the logic applied in tblRemoved?  If not, can you do me a favour and highlight the exact output you want based on the rows you have in the sample data?

[lotus22]

@TheoC  The entries removed should only be late and not completed. The completed is good.

[TheoC]

Yes, @lotus22.  I understand that.  That is what I did already in the 'tblRemoved' table for you in the PBIX file I attached earlier.  If this isn't the output you want, can you please tell me which rows are the output you want based on your sample data.

[lotus22]

We only need to remove late entries where subsequent for the same Machine and Operator are within 5 minutes.

We look for first late entry, capture machine and operator. Look for similar entry with same Machine and Operator in next 5 minutes. If there is nothing then we register the late entry.

If there is entry within 5 minutes, then we remove the late entry.

The goal is that operator has fixed the late entry in 5 minutes or less.

[TheoC]

@lotus22 

I don't know how to ask this in any different manner and there's clearly a miscommunication happening somewhere, so I will make one more effort to help in this instance then I will need to allow another member of the Community assist with this post:

Question:  Which of the rows in your sample data meet the requirement you are wanting?  

The expected response should list ID #152, #153..... #170...  

Please tell me what you want the output to be based on your sample data.

Theo

[lotus22]

@TheoC  Thank you and sorry for the confusion. I simplified the data little bit and revert back to raw data.  In Raw, we have item requested from Operator. If it is requested then there should be response back. The response  can come from operator or the supervisor. But there has to be response for that machine. If the response doesn't come in 5 mins then we mark it "LATE". If it does come back in 5 minutes, then we mark it "DELETE"

I have added Mark Column and also the comments. hope this helps Please see attached

Id	Created	CreatedBy	Modified	ModifiedBy	Deleted	State	Machine	Custom	Shift	Mark
159	8/29/2024 9:15	10314			FALSE	1	RW100 Side A	Pass	1
158	8/29/2024 9:15	10314			FALSE	2	RW100 Side A	Fail	1
157	8/29/2024 9:13	54114			FALSE	4	BC213	Complete	1
156	8/29/2024 9:13	54114			FALSE	1	BC213	Pass	1
155	8/29/2024 9:12	54167			FALSE	1	CA210	Pass	1
154	8/29/2024 9:12	54167			FALSE	4	CA210	Complete	1
150	8/29/2024 9:04	33868			FALSE	1	RW210	Pass	1
149	8/29/2024 9:04	33868			FALSE	4	RW210	Complete	1
148	8/29/2024 9:02	10326			FALSE	4	WC006	Complete	1
147	8/29/2024 9:00	10235			FALSE	3	CA210	Requested	1	Late	Mark it late as the next entry is at 154 which is more than 5 mins
146	8/29/2024 8:59	54114			FALSE	3	BC213	Requested	1	Late	Mark it late as the next entry is at 156 which is more than 5 mins
109	8/29/2024 7:10	34989			FALSE	1	WC100 Side A	Pass	1
106	8/29/2024 7:09	10270			FALSE	4	RW002	Complete	1
105	8/29/2024 7:09	10270			FALSE	3	RW002	Requested	1
104	8/29/2024 7:08	34989			FALSE	4	WC100 Side B	Complete	1
103	8/29/2024 7:08	34989			FALSE	1	WC100 Side B	Pass	1
102	8/29/2024 7:08	34989			FALSE	3	WC100 Side B	Requested	1
101	8/29/2024 7:05	10317			FALSE	4	WC011 Side A	Complete	1
100	8/29/2024 7:05	10317			FALSE	1	WC011 Side A	Pass	1
99	8/29/2024 7:05	53914			FALSE	4	PI001	Complete	1
98	8/29/2024 7:04	10317			FALSE	3	WC011 Side A	Requested	1
78	8/29/2024 0:59	53828			FALSE	3	FT005	Requested	3	Late	Mark it late as the next entry is at 85 which is more than 5 mins
66	8/28/2024 15:18	54245			FALSE	4	PI002	Complete	2
65	8/28/2024 15:13	54365			FALSE	3	PI002	Requested	2	Delete	Mark it delete since we have 66 even though its different users, could have been supervisor card
64	8/28/2024 15:10	54098			FALSE	4	FT006	Complete	2
63	8/28/2024 15:10	54098			FALSE	1	FT006	Pass	2
62	8/28/2024 15:10	54098			FALSE	3	FT006	Requested	2
60	8/28/2024 15:09	53670			FALSE	4	WC004 Side B	Complete	2
61	8/28/2024 15:09	53955			FALSE	4	WC011 Side A	Complete	2
59	8/28/2024 15:09	53955			FALSE	1	WC011 Side A	Pass	2
58	8/28/2024 15:08	53670			FALSE	1	WC004 Side A	Pass	2
57	8/28/2024 15:08	53955			FALSE	3	WC011 Side A	Requested	2	Delete	Mark it delete since we have 59
56	8/28/2024 15:07	54171			FALSE	1	WC008 Side A	Pass	2
55	8/28/2024 15:07	54171			FALSE	4	WC008 Side A	Complete	2
54	8/28/2024 15:07	53955			FALSE	4	WC011 Side B	Complete	2
53	8/28/2024 15:07	53955			FALSE	1	WC011 Side B	Pass	2
52	8/28/2024 15:06	54171			FALSE	3	WC008 Side A	Requested	2	Delete	Mark it delete since we have 55
51	8/28/2024 15:06	53670			FALSE	4	WC004 Side B	Complete	2
50	8/28/2024 15:06	54171			FALSE	4	WC008 Side B	Complete	2
49	8/28/2024 15:06	54171			FALSE	1	WC008 Side B	Pass	2
48	8/28/2024 15:05	53955			FALSE	3	WC011 Side B	Requested	2	Delete	Mark it delete since we have 53
47	8/28/2024 15:05	54370			FALSE	4	WC100 Side A	Complete	2
46	8/28/2024 15:05	54370			FALSE	1	WC100 Side A	Pass	2
45	8/28/2024 15:05	54370			FALSE	3	WC100 Side A	Requested	2	Delete	Mark it delete since we have 46
44	8/28/2024 15:05	53670			FALSE	1	WC004 Side B	Pass	2
43	8/28/2024 15:05	53712			FALSE	1	FT004	Pass	2
42	8/28/2024 15:05	53712			FALSE	4	FT004	Complete	2
41	8/28/2024 15:04	19722			FALSE	4	WC001	Complete	2
40	8/28/2024 15:04	53712			FALSE	3	FT004	Requested	2	Delete	Mark it delete since we have 42
39	8/28/2024 15:04	54370			FALSE	4	WC100 Side B	Complete	2
38	8/28/2024 15:04	54370			FALSE	1	WC100 Side B	Pass	2
37	8/28/2024 15:04	53988			FALSE	1	FT005	Pass	2
36	8/28/2024 15:04	53988			FALSE	4	FT005	Complete	2
35	8/28/2024 15:04	54171			FALSE	3	WC008 Side B	Requested	2	Late	Mark it late since we do not have subsequent entry within 5 mins
34	8/28/2024 15:03	53988			FALSE	3	FT005	Requested	2	Late	Mark it late since we do not have subsequent entry within 5 mins
33	8/28/2024 15:03	54370			FALSE	3	WC100 Side B	Requested	2	Late	Mark it late since we do not have subsequent entry within 5 mins
32	8/28/2024 15:02	19722			FALSE	3	WC001	Requested	2	Late	Mark it late since we do not have subsequent entry within 5 mins

[TheoC]

Hi @lotus22 

I've done this in Power Query for you.

There are two tables in the attached PBIX.  One is filtered (with the > 5 mins remaining).  The other keeps both >5 mins and <5 mins in but I've added a validation column for you.

The approach I applied was:

1. Create a new Custom Column to show the next entry with the same machine and operator.
2. Calculated the time difference between the entries for the above.
3. Filtered out entries with a time difference greater than 5 mins.

Let me know if you need this in DAX otherwise the PQ version is attached.  

Hope this helps! 🙂

Theo

[lotus22]

@TheoC  Thank you so much! That was quick.

We only need to validate if the previous entry is LATE and the next entry is on time. As soon as we get a Late entry for a particular machine, we must ensure another entry is on time within 5 minutes. If yes, then we delete the late entry; otherwise, it shows up.

For instance, if 158, a late entry, appears and we know that 159, a non-late entry, follows within 5 minutes, we can 'exclude' 158. Our focus is solely on validating late entries. 

It gets tricky. If RW100 Side A  has entry 158 that is late but there was no late entry, we include this on the graph. Now, after two hours, if RW100 Side A has another late entry, say 250, that will be a new validation but not part of 158.