Duration of usage in an app

[Anonymous]

Hi experts,

I have an app and i want to know the duration (x amount of seconds) a user is using my app. The end result should be something like X user (appid) has used the features (Event) for x seconds/minutes/hours.

Let me give you a snapshot of my data:

What i try to illustrate here, is the duration i want to end up with (From 15:38:14 to 15:39:21 = 2 min 7 seconds).
As you can see i do not have an END TIME. This is my biggest challenge.

I've realised that the sessions change (SessionID), and i believe it changes when the app is shut down/put in the background. I can see it changes every day (continually) and also that the sessions changes when some time has gone from one event to another. Also it changes sometimes when the event "Applaunch" is created.

With this in mind - as you can see in on the right side "Session ID" - would you assume that using the unique session ID would be the way to find the duration?

How would you do this?

Every little help is appreciated.

Thank you very much 🙂

[TomMartens]

Hey,

it's difficult to recommend anything without detail knowledge about the tracking system of your app.

Nevertheless, if you create a pbix file with the sample data, upload the pbix to onedrive or dropbox, then it will be much more simple to create a DAX statement that calculates the duration.

Regards,

Tom

[Anonymous]

Hi TomMartens,

Thank for the reply! 

I will upload the sample data in a PBIX

[Anonymous]

This is the link for the PBIX file:

https://www.dropbox.com/s/71hbmki6rbs0jne/SamplePbixUsage%28Community%29.pbix?dl=0

And this is the link for the raw data excel file:

https://www.dropbox.com/s/nr34mvjeaszyz1s/SampleDataUsage%28Community%29.xlsx?dl=0

Thanks to anyone, who can help 🙂

[TomMartens]

Hey,

thanks for providing sample data, this makes things much more simple. Please be aware that this is just a first guess. I'm somewhat uncertain about the "feature type". As the same feature is used more than once during one session. Can you please elaborate a little more about the expected result, especially about the duration for the last event during one session.

I used this DAX statement to create a new table (Menu Modeling --> New Table):

Summarized Table = 
ADDCOLUMNS(
    ADDCOLUMNS(
        SUMMARIZE(
            'Sheet1'
            ,Sheet1[Appid]
            ,Sheet1[SessId]
        )
        ,"SessionStart", CALCULATE(MIN('Sheet1'[EventTime - Start]))
        ,"SessionEnd", CALCULATE(MAX('Sheet1'[EventTime - Start]))
    )
    ,"Duration",DATEDIFF([SessionStart],[SessionEnd],SECOND)
)

Hopefully this is what you are looking for.

Regards,

Tom

[Anonymous]

Hi Tom,

Huge help.

Featuretype to you mean EventType?

The EventType is different areas of the application. I need to filter on EventType in Powerbi and still be able to find the duration of a session. An example is to filter on 3 EventTypes and see the duration of those in a session. 

I hope this makes sense.

My ultimate goal when having this duration, is to count the amount of AppId's who has a duration of over x amount of seconds with filtered EventTypes. Lets call it a definition of an engaged user 🙂

[TomMartens]

Hey,

Yes I was referring to EventType - sorry.

Understand and makes sense 🙂

Can you please elaborate how to consider the last event type in one session, as the StartTime of the EventType in that specific session has no end, this problem is also exisiting if there is just one EventType in the same session. Even if the latter is not likely, it will break the calculation if it's not considered.

Regards,

Tom

[Anonymous]

Very good question - actually haven't thought about that.

The only thing i can think of is to use the last timestamp in the session as the endtime, but this would obviously not be correct.

My problem has from the start been the EndTime missing. 

[TomMartens · Accepted solution]

Hey,

okay, so here is a little more complex DAX statement (creating a table), if there is no subsequent event (this is is the case for the last eventtype in a session or if there is just one) the startime will be used.

There is another issue, as Power BI just provides a second using the DATEDIFF function, there is an issue with precision, this results to a duration of zero. If you need more precision, then you / we have to leverage more complex calculations, but this would also mean, that the starttime has to be more precise.

Nevertheless, here is the (new) DAX statement:

Summarized Table = 
ADDCOLUMNS(
    ADDCOLUMNS(
        SUMMARIZE(
            'Sheet1'
            ,Sheet1[Appid]
            ,Sheet1[SessId]
            ,Sheet1[EventTime - Start]
            ,Sheet1[EventType]
        )
        ,"SessionEnd", 
                var thisAppID = [Appid]
                var thisSessID = [SessId]
                var thisSessionStart = [EventTime - Start]
                var theEnd = 
                    CALCULATE(
                        MIN('Sheet1'[EventTime - Start])
                        ,ALL('Sheet1'[EventType])
                        ,FILTER(
                            'Sheet1'
                            ,'Sheet1'[Appid] = thisAppID && 'Sheet1'[SessId] = thisSessID && 'Sheet1'[EventTime - Start] > thisSessionStart
                        )
                    )
                return
                IF(ISBLANK(theEnd), thisSessionStart, theEnd)
                
    )
    ,"Duration",DATEDIFF([EventTime - Start],[SessionEnd],SECOND)
)

And here is a screenshot of a table visual using the calculated table:

Regards,

Tom