Forum Discussion
Find Value in Same Table
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 ) ) )
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.
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
)
)
)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!