Forum Discussion

KayceVC's avatar
KayceVC
Helper II
7 years ago
Solved

Find Record in Same Table

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?

Need Help
  • v-diye-msft's avatar
    v-diye-msft
    7 years ago

    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_kByhWoqBsTqbLLLbTIvT1whg?e=ZmdKGU

     

    Best regards,

    Dina Ye

2 Replies

  • v-diye-msft's avatar
    v-diye-msft
    Community Support
    7 years ago

    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_kByhWoqBsTqbLLLbTIvT1whg?e=ZmdKGU

     

    Best regards,

    Dina Ye

    • v-diye-msft's avatar
      v-diye-msft
      Community Support
      7 years ago

      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