Power BI running out of memory with DAX formula

[AndrewWstry]

I keep getting a memory error when trying to perform the below formula. Does anyone know of a lighter way to achieve the same result? I am doing it this way because some employees may login and logout more than once per day so I cannot just use a Min and Max for login/logout. Any help is appreciated!

Login Duration =

VAR __thislogin = 'UserHistory'[Event Time Convert]

VAR __nextlogout =

CALCULATE (

MIN( 'UserHistory'[Event Time Convert] ),

ALLEXCEPT( 'UserHistory', 'UserHistory'[Content.userId] ),

'UserHistory'[Event Time Convert] > __thislogin,

'UserHistory'[Content.event] = "LogOut"

)

RETURN

IF (

'UserHistory'[Content.event] = "Login",

DATEDIFF (__thislogin, __nextlogout, MINUTE ),

BLANK ()

)

[ichavarria]

Hi @AndrewWstry,

One possible reason for a memory error could be that the amount of data being processed is too large for your system's memory capacity. Here are a few potential solutions to this issue:

1. Filter the data: You can filter the data in the 'UserHistory' table to only include the columns and rows that you need for this calculation. This can reduce the amount of data that needs to be loaded into memory.

2. Use SUMMARIZECOLUMNS instead of ALLEXCEPT: You can try using the SUMMARIZECOLUMNS function instead of the ALLEXCEPT function to create a virtual table with the unique values of the 'Content.userId' column. This may be more memory-efficient than using ALLEXCEPT.

3. Use an iterative calculation: You can try using an iterative calculation using the 'EVENTORDER' function to avoid the need to load all the data into memory at once. This can help reduce memory usage.

4. Increase memory capacity: If none of the above solutions work, you may need to consider increasing your system's memory capacity to accommodate the amount of data being processed.

Here's an example of how you could use SUMMARIZECOLUMNS to create a virtual table with the unique values of the 'Content.userId' column:

Login Duration =
VAR __thislogin = 'UserHistory'[Event Time Convert]
VAR __filtered =
FILTER (
'UserHistory',
'UserHistory'[Content.event] = "LogOut"
|| 'UserHistory'[Content.event] = "Login"
)
VAR __nextlogout =
CALCULATE (
MIN( 'UserHistory'[Event Time Convert] ),
ALLEXCEPT( __filtered, 'UserHistory'[Content.userId] ),
'UserHistory'[Event Time Convert] > __thislogin,
'UserHistory'[Content.event] = "LogOut"
)
RETURN
IF (
'UserHistory'[Content.event] = "Login",
DATEDIFF (__thislogin, __nextlogout, MINUTE ),
BLANK ()
)

I hope this helps! Let me know if you have any further questions.

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly

[AndrewWstry]

Thank you so much for the response! I did not even think of trying it this way however I am getting the below error:

The ALLEXCEPT function expects a table reference for argument '1', but a table expression was used.