Forum Discussion
[DAX] Check for rows between 2 rows
Hey Power BI Community,
I have a problem, that I'd like some help on. I generally know a bit of coding unfortunately DAX still feels very unnatural to me. Anyhow here it comes.
I have a certain amount of sessions. A session consist of an ID DateTime and URL.
Now we have defined (for now) a succesful session if someone does a search, then ends up chatting. However in that session, it could be that the person got to a new chat through a different route. So there's a catch.
This is what one session looks like:
So basically first I want to isolate whatever I'm going to calculate to one specific ID (one session) AllExcept I guess...?
Then I want to order that based on DateTimeStart (changed the type already).
Then I want to check where URL /searchboardresults is (if it exists). Then check if the URL /newchat comes after based on DateTimeStart.
And lastly it needs to check in between if URL /Connections OR /Favorites
If it meets all those requirements, we've got a succesful session.
I wonder if this kind of complexity is even possible in DAX. But surely I'm at the moment kind of lost in a process that seems like coming to no solution...
I hope I made my point clear.
Any ideas? Tips? Something that will guide me in the right direction?
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
7 Replies
Hey,
currently I#m not able to tell you if this would be possible using DAX, this is simply because I'm not able to fully understand your question, and what you are going to achieve finally.
Im not able to understand why there are some "true" values in the loggedin column, maybe it would be helpful if you can provide a link to an excel file, that contains sample data.
Cheers,
Tom
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.
