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