Using calculated column to find duration of app usage

[Anonymous]

Hi guys,

This question is based on a previous topic:

https://community.powerbi.com/t5/Desktop/Duration-of-usage-in-an-app/m-p/681482#M328233

I have been working with the calculated column that @TomMartens helped me with.
The problem is now, that I can not run the calculated column because of lack of memory (18 million rows).

I am wondering if there is any other way to create the "Duration" column?

This is the calculated column with slight changes from my previous post:

Tabel = ADDCOLUMNS(
    ADDCOLUMNS(
        SUMMARIZE(
            'FullExtract2017-2025'
            ;'FullExtract2017-2025'[AppId]
            ;'FullExtract2017-2025'[SessId]
            ;'FullExtract2017-2025'[EventTime - Start]
            ;'FullExtract2017-2025'[Feature_Key]
            ;'FullExtract2017-2025'[Country_Key]
            ;'FullExtract2017-2025'[UserInfo_Key]
            ;'FullExtract2017-2025'[SessionNumber_Key]
            ;'FullExtract2017-2025'[Module_Key]
        )
        ;"SessionEnd"; 
                var thisAppID = [Appid]
                var thisSessID = [SessId]
                var thisSessionStart = 'FullExtract2017-2025'[EventTime - Start]
                var theEnd = 
                    CALCULATE(
                        MIN('FullExtract2017-2025'[EventTime - Start])
                        ;ALL('FullExtract2017-2025'[Feature_Key])
                        ;FILTER(
                            'FullExtract2017-2025'
                            ;'FullExtract2017-2025'[AppId] = thisAppID && 'FullExtract2017-2025'[SessId] = thisSessID && 'FullExtract2017-2025'[EventTime - Start] > thisSessionStart
                        )
                    )
                return
                IF(ISBLANK(theEnd); thisSessionStart; theEnd)
                
    )
    ;"Duration";DATEDIFF('FullExtract2017-2025'[EventTime - Start];[SessionEnd];SECOND)
)

I am querying the data from an SQL database.

Please tell me if you need additional information.

Thank you!

[ImkeF · Accepted solution]

Hi @Anonymous 

I've exerienced memory-problems with such a calculation as well. Using Power Query Group-function for "thisAppId" and "thisSessionId" could solve your case here:

https://www.thebiccountant.com/2018/09/30/memory-efficient-clustered-running-total-in-power-bi/

Please check if you can adapt it to your case, otherwise pls come back.

[Anonymous]

This looks very interesting!

I will have a look - thank you so much