Forum Discussion
[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
Thanks for the sample, I will have closer look later today.
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
Hi Tom,
Thanks for this great answer. It is close to the solution and definitely gave me some insight in "how to think" the right way in problem solving in Power BI. However, thanks to my bad communication skills it is not yet entirely solved. I will think for myself but if you'd like to help it surely will be appreciated. At one part you do a LookUpValue step. In here you define it to look up the value 2 before chat.
However, unfortunately 2 should be an variable. So x-y for example. Here's why:
The session I showed was just an example of what a session could look like. How it would look like in any case is this (just the URL's in a succesful session).
- x Amount of rows
- search
- y amount of rows
- chat
this would be a succesful session
This would not be a succesful section
- x amount of rows
- search
- y amount of rows
- connections
- z amount of rows
- chat
A session could even look like this
- x amount of rows
- search
- y amount of rows
- search
- z amount of rows
- chat
- q amount of rows
- connections
(succesful session because search and chat are not interrupted by connections)
But it could also look like this
- x amount of rows
- chat
- search
- y amount of rows
- search
- z amount of rows
- connections
- chat
(non succesful session because between the potentiontial search and chat there is connections)
because now it means the person went first to another page that allows him to start a chat (connections).
We want to measure how many searches lead to an actual chat, to see how much success we book with our search results.
Thanks for all the help so far anyway!
Hey Sam,
from your description I deduct the following business rule(s):
a session can be considered successful if a sub-sequence starting with "/search" ending with "/chat" within the session is not interrupted by "/connections"
That means you have to detect for any /chat if the last preceeding index of a /search is greater than the index of the last preceedng /connections
I guess this can be accomplished, but unfortunately I have to focus on something different right now
Alright you're deduction is correct. I'll take it from here thanks for your help.
