How do I build "logic" that pulls data from two different table rows

[Anonymous]

I have a table of events.    Each event is a row in the table, and  has a time & date stamp, an event "Type" and each event is associated with an activity, identified by an activity ID.  

As an example lets say there are 4 different types of events.  "Create", "Error", "Ready", "Complete".

For any given activity, "Ready" events can occur after a "Create" Event, or after an "Error" Event.  I want to create table visualization that displays only those "Ready", where the previous event for that activity was an "Error" event.

I'm relatively new to PowerBI and drawing a complete blank on how I would accomplish this.  Any suggestions?

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

Based on  your description, I have created a simple sample:

Please try:

Apply the measure to the table visual's filter:

Flag = 
var _a = MAXX(FILTER(ALL('Table'),[Activity]=SELECTEDVALUE('Table'[Activity])&&[Date Time]<SELECTEDVALUE('Table'[Date Time])),[Date Time])
var _b = MAXX(FILTER(ALL('Table'),[Activity]=SELECTEDVALUE('Table'[Activity])&&[Date Time]=_a),[Type])
return IF(SELECTEDVALUE('Table'[Type])="Ready"&&_b="Error",1)

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.