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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

Identify penultimate value from a tale based on final event string & date

I have a table ('M Jobs') with job reference numbers ([DeptJobNumber]), Job Events ([JobEventDescription]) and Event Times ([JobEventDateTime] which holds the lifecycles of jobs identified by Events and their timestamps.


I also have a list of specific event strings, which I need to use to identify the last occurance of each of these events prior to the job being completed (the "Practically Complete" event).


For Example, a job starts it's life once it has been "ENTERED" and is being measured until it hits the "PRACTICALLY COMPLETE" event.

I need to pull the last occurance of a listed event prior to the job being completed.


In the example below, we can see that the last listed event prior to the job being "Practically Complete" (10/01/2020 16:24:59) was "Gas No Access NTI" (10/01/2020 15:55:38).


I would therefore need to pull this event and time along with the job number, start date and completion date.


I have attempted to do this by creating a summarised table but I am still not getting the correct results (see below). I have attempted to use || to define the specific strings but the query fails when trying to convert the string to a boolean.


Please, can someone help me out with this as I'm having a nightmare!!  


Summary Table

Summary Table.PNG


Specific Events List

Measured Events.png


Jobs & Events Table

Job Lifecycle.PNG


Helpful resources

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Fabric certifications survey

Certification feedback opportunity for the community.