Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

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?

 

 

Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    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.

1 Reply

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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.