[DAX] Check for rows between 2 rows

Hey,

here is my solution, hope it matches your requirement :-)

My solution is based on the following assumption:

Each session (your column "id") can be considered as a sequence of events (your column "page" ordered by your column "Date Time").

Each sequence can be flagged as successful if both of these two rules are met:

• The sequence contains an event "/StartChat" (indexposition in that sequence = x)
• The event in that sequence at the indexposition x - 2 equals "/Search"

My solution consists of four steps:

• Power Query: Indexing Rows in a group (the sequence)
• Dax - Calculated Column: Using LookupValue to get the previous, previous event
• DAX - Calculated Column: compare if the prepre event equals "/Search"
• DAX - Calculate Column: flag the session

After applying the above mentioned steps the table (based on your sample file) looks like this:

You can find my pbix file here:

https://www.dropbox.com/s/vg676uq81ep1gaz/values%20from%20previous%20rows.pbix?dl=0

If you have look at the query, you will see the query step "GroupedRows". This step creates a row index in each group. I explained this method in more detail here https://minceddata.wordpress.com/2017/01/07/solving-generic-data-processing-problems-using-sql-r-and-power-bi/

In the Power BI data model I created the three calculated columns "prepreEvent", "prepreOfStartIsSearch", and "sessionIsSuccessful"

Hope this helps