Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cjd72consulting
Frequent Visitor

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?

 

 

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

Hi @cjd72consulting ,

 

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

vjianbolimsft_0-1684134591371.png

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)

vjianbolimsft_1-1684134943140.png

Final output:

vjianbolimsft_2-1684134961936.png

 

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.

View solution in original post

1 REPLY 1
v-jianboli-msft
Community Support
Community Support

Hi @cjd72consulting ,

 

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

vjianbolimsft_0-1684134591371.png

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)

vjianbolimsft_1-1684134943140.png

Final output:

vjianbolimsft_2-1684134961936.png

 

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.