Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
Hi @KayceVC
I’ve created a sample as your requested, figure that you’d like to return
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
Hi @KayceVC
I’ve created a sample as your requested, figure that you’d like to return
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |