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...
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

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