cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sam1220 Frequent Visitor
Frequent Visitor

[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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: [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:

2017-01-10_15-02-25.png

 

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...

 

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

 

Hope this helps 

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

View solution in original post

7 REPLIES 7
Super User III
Super User III

Re: [DAX] Check for rows between 2 rows

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
sam1220 Frequent Visitor
Frequent Visitor

Re: [DAX] Check for rows between 2 rows

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:

 

Capture2.PNG

 

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

 

Download Sample

Super User III
Super User III

Re: [DAX] Check for rows between 2 rows

Thanks for the sample, I will have closer look later today.

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
Highlighted
Super User III
Super User III

Re: [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:

2017-01-10_15-02-25.png

 

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...

 

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

 

Hope this helps 

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

View solution in original post

sam1220 Frequent Visitor
Frequent Visitor

Re: [DAX] Check for rows between 2 rows

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!

 

Super User III
Super User III

Re: [DAX] Check for rows between 2 rows

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
sam1220 Frequent Visitor
Frequent Visitor

Re: [DAX] Check for rows between 2 rows

Alright you're deduction is correct. I'll take it from here thanks for your help.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors