How to calculate duration between events in a events table

[leonardosilva]

Hi there,

I have a database that contains a list of users events in my App, basically where and when they click. The collumns I have are User ID, event type (as login, product selection, cart, payment options and confirmation) and time (in timestamp). I wanna know how many time users spend in each action using this data.

This can be done by creating a collumn that contains the time diference between two consectutive events of the same user (the first event can be ignored), but I coudn´t find a way to realize this. I have also tried to use the function EARLIER, but since my database is 20000 rows long, it keeps loading for minutes until I give up on it.

Can Power BI do this?

[vivran22]

Hello @leonardosilva ,

Can you please share a sample data set along with the expected outcome?

Cheers!
Vivek

https://www.vivran.in/
Connect on LinkedIn

[leonardosilva]

This is the desired outcome and an example I did with the EARLIER function:

When I applied this to the original database, it took 26 minutes to display a mensage saying I don´t have enough memory (I have 8GB) to make the process.

[v-frfei-msft]

Hi @leonardosilva ,

Please try this one.

Column = 
VAR t = 'Plan1'[timestamp]
VAR last =
    CALCULATE (
        MAX ( 'Plan1'[timestamp] ),
        FILTER (
            'Plan1',
            'Plan1'[timestamp] < t
                && 'Plan1'[userid] = EARLIER ( 'Plan1'[userid] )
        )
    )
RETURN
    t - last

[leonardosilva]

Hi, @v-frfei-msft 

I´ve tried this solution in my database but it also took 35 minutes and again displayed a mensage saying I don´t have enought memory. This is probably happening because my database is 20000+ rows long. Do you know another way to solve this using less memory?