Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
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:
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.
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.
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.
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
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
85 | |
82 | |
66 | |
49 |
User | Count |
---|---|
137 | |
111 | |
101 | |
66 | |
65 |