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
Simple said I want to be able to identify of each session (which contains a unique ID) wether it is succesful or not.
A session is succesfull if the user did a search and from there got to chat with someone else.
So sample data would be:
/login
/homepage
/searchresults
/company this would be if he clicked on a result
/startchat
This would be a succesful session because the user made a search, got to a company page and started a chat.
However it could be that the user (through another journey) got to start a chat.
So in that case it could look like this:
/login
/homepage
/search
/connections
/startchat
This would not be a succesful session. Because in between went to connections and from there started a chat.
So how would this look like in sample data:
Green would be the only succesful session. Because looking at the order of date of the second one. You will see that between search and start chat, the user went to /connections.
Ive added this sample data as Link
Hope this helps and thanks for the help in advance
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