Find Record in Same Table

[KayceVC]

I have a table with audit logs. The records share a common parent record ID, but have unique ID's for the child record that records each action performed on the parent. For security reasons, I am not permitted to post any actual data, but I can give a brief example of how the table is formatted from the SQL import.

What I need to accomplish is isolating a particular event (KeyID 22) and then determining if a seperate event (KeyID 41) happened on the same day for the same user after the triggering event. I added two indexes to my table, one for the overall record order and one that orders the parents.  I tried using a LOOKUPVALUE nested in an IF to find where the 22 happened and additional records for the same parent existed, but since I don't know where the next record could happen, I am not certain what to feed into the searchvalue parameter. 

My minimal goal would be to return the date field from the next instance where  KEYID 41 happened and I can perform additional calculations off that. In a perfect world, I would return a 1 or 0 (as this is a value I need to ultimately count/sum) based on both events triggering on the same day with 41 happening after 22. If a single 41 happens after multiple 22s, this is a permitted use case. 

Does anyone have any suggestions to get me started on this?

[v-diye-msft · Accepted solution]

Hi @KayceVC 

I’ve created a sample as your requested, figure that you’d like to return

• The date of [keyID]=41 with the condition that: after 22&&same date&&same user.
• 1/0 based on above conditions

Please use below measures:

Measure = var I = CALCULATE(MIN([Index]),FILTER(ALL(Table1),[KeyID]=22),VALUES(Table1[Date]),VALUES(Table1[User]))
Return 
IF(ISBLANK(I),0,IF(CALCULATE(MAX([KeyID]),FILTER(Table1,Table1[Index]>I))=41,1,0))

Measure 2 = var I = CALCULATE(MIN([Index]),FILTER(ALL(Table1),[KeyID]=22),VALUES(Table1[Date]),VALUES(Table1[User]))
var a = CALCULATE(MAX([KeyID]),FILTER(Table1,Table1[Index]>I))
Return 
IF(ISBLANK(I),BLANK(),IF(CALCULATE(a)=41,CALCULATE(MAX([Date]),FILTER(Table1,a=41)),BLANK()))

Attached pbix for your reference: https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/EZ2IhvB-qQZBlMOsaPNA3_...

Best regards,

Dina Ye

[v-diye-msft]

Hi @KayceVC 

If my above post helps, could you please consider Accept it as the solution to help the other members find it more quickly. thanks!

Best regards,

Dina Ye