Find Value in Same Table

[KayceVC]

I have an audit table which contains audit log data. I am trying to isolate when a particular event happens, if another event happens on the same day for the same user. 

In this example, I would be looking for when Key_ID 22 happens, is it followed by a 41 on the same date by the same user. I tried using a LOOKUPVALUE function nested in an IF, but since I did not know if or where the record could occur, I could not figure out what value to input for the search value parameter.  I do have 2 multiple index on the table, one for all the table records and one for parent records that shows what order the child records happened in. 

What I am looking for at a minimum is to return the date the 41 event happened on or a 1 if there is no 41. In a perfect world, I would have the column returning a 0 for instances where the 41 followed the 22 on the same day by the same user and a 1 when this condition is not met. 

Any help to get me moving on this would be appreciated. 

[PattemManohar]

@KayceVC  Could you please post the sample data that can be copied instead of screenshot. Also, what is your expected output... Please post the expected output as well.

Is it just to list ParentIDs and Flag (if there is a Key_ID 22 and followed by 41 for same user on same day)

[jdbuchanan71]

Hello @KayceVC ,

Can you add the result column to your screen shot for what you would like to see?

ParentID	ChildID	Key_ID	User	Date_Updated	TableIndex	RecordIndex	Result
4061	2232	21	Aguy	6/27/2019	15	1
4061	2233	22	Aguy	6/27/2019	16	2
4061	2234	2	Aguy	6/27/2019	17	3
4061	2235	22	Aguy	6/27/2019	18	4
4061	2236	3	Aguy	6/27/2019	19	5
4061	2237	41	Aguy	6/27/2019	20	6
4061	2238	1	Agirl	6/27/2019	21	7
4061	2239	3	Agirl	6/27/2019	22	8
4061	2240	1	Agirl	6/27/2019	23	9

[KayceVC]

Hey @jdbuchanan71 

This would be what I would expect. I would like to return the date where the 41 on the same day occurred on the row item for the associated 22. This will allow me to calculate the 2 follow up values I need a boolean for was the 41 record (a time entry note) updated within the allowed time (before the end of the day) and then a calculation for how long it took for each 22 record to receive the time entry update. 

ParentID	ChildID	Key_ID	User	Date_Updated	TableIndex	RecordIndex	Result
4061	2232	21	Aguy	6/27/19 1:00	15	1
4061	2233	22	Aguy	6/27/19 2:00	16	2	6/27/19 2:45
4061	2234	2	Aguy	6/27/19 2:15	17	3
4061	2235	22	Aguy	6/27/19 2:30	18	4	6/27/19 2:45
4061	2236	3	Aguy	6/27/19 2:30	19	5
4061	2237	41	Aguy	6/27/19 2:45	20	6
4061	2238	1	Agirl	6/27/19 3:05	21	7
4061	2239	3	Agirl	6/27/19 3:15	22	8
4061	2240	1	Agirl	6/27/19 4:00	23	9

@PattemManohar Unfortunately this is as much sample data as I can provide. It is formatted exactly how my real data is, but due to the senstive nature of my data, I am not permitted to post actual system data. The above sample is very close to my ideal input/output though.

---

[jdbuchanan71 · Accepted solution]

Hello @KayceVC 

Give this a try.  I had a check for the time on the 42 being after the time on the row but it doesn't read like you need that but I left it in the code and just commented it out.

Result = 
VAR TheDate = DATEVALUE ( auditdata[Date_Updated] )
VAR TheDateTime = auditdata[Date_Updated]
VAR TheUser = auditdata[User]
VAR NumRows = 0
RETURN
    IF (
        auditdata[Key_ID] = 22,
        CALCULATE (
            MAX ( auditdata[Date_Updated] ),
            FILTER (
                auditdata,
                auditdata[User] = TheUser
                && auditdata[Key_ID] = 41
                && DATEVALUE ( auditdata[Date_Updated] ) = TheDate
                --&& auditdata[Date_Updated] > TheDateTime
            )
        )
    )

[KayceVC]

Sorry for getting back to this so late, my project priority got reassigned. 

Your solution worked brilliantly and very quickly. I was afraid with all the steps it might be a long running column, but it updated in less than 5s. This was exactly what I needed, thank you so much!