Forum Discussion
Not enough memory to complete this operation
- 3 years ago
Hello Anonymous ,
ah sorry, I thought you get the error when you apply in PowerQuery.
Yes, in the calculated column I get the same error. From my point of view this comes from the EARLIER function, that can be quite expensive.
In general it's not recommended any more to use EARLIER as the function is quite confusing. The recommendation is to use variables instead.
Here from dax.guide:It is recommended using variable (VAR) saving the value when it is still accessible, before a new row context hides the required row context to access the desired value.As I didn't have the initial result, I can't really compare. But I think the following calculated column should produce the result you desire and without performance issues:
MaxLastHotTime_New = VAR vTractorCurrentRow = 'new vessel'[TRACTOR_NO] VAR vVoyageCurrentRow = 'new vessel'[VoyageID] VAR vNewOnChassisCurrentRow = 'new vessel'[New On Chassis Time] RETURN CALCULATE( MAX('new vessel'[New Off Chassis Time]), ALL('new vessel'), 'new vessel'[TRACTOR_NO] = vTractorCurrentRow && 'new vessel'[VoyageID] = vVoyageCurrentRow && 'new vessel'[New On Chassis Time] < vNewOnChassisCurrentRow )Let me know if that works for you.
If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
Best regards
Denis
Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
Thanks selimovd ,
Yes, it is very strange, and I really have no idea why it happens with this simple DAX.
The data source is just a 4MB excel file, and I didn't do any crazy transformation..... just very ordinary I can tell.
Below is my the shared PBIX file.
Hello Anonymous ,
I cannot reproduce the behavior.
For me it takes about 5 seconds to refresh the whole file.
Can you reboot and try again? Or maybe you have another computer or a VM to try?
Best regards
Denis
- Anonymous3 years agoNot applicable
Thanks, selimovd
Really?! You mean you succeed to have values under the calculated column "MaxLastHotTime"?
Btw, what is your computer's specification?
- Anonymous3 years agoNot applicable
I tried once right after rebooting my computer, but still can't get the values...
- selimovd3 years agoMost Valuable Professional
Hello Anonymous ,
ah sorry, I thought you get the error when you apply in PowerQuery.
Yes, in the calculated column I get the same error. From my point of view this comes from the EARLIER function, that can be quite expensive.
In general it's not recommended any more to use EARLIER as the function is quite confusing. The recommendation is to use variables instead.
Here from dax.guide:It is recommended using variable (VAR) saving the value when it is still accessible, before a new row context hides the required row context to access the desired value.As I didn't have the initial result, I can't really compare. But I think the following calculated column should produce the result you desire and without performance issues:
MaxLastHotTime_New = VAR vTractorCurrentRow = 'new vessel'[TRACTOR_NO] VAR vVoyageCurrentRow = 'new vessel'[VoyageID] VAR vNewOnChassisCurrentRow = 'new vessel'[New On Chassis Time] RETURN CALCULATE( MAX('new vessel'[New Off Chassis Time]), ALL('new vessel'), 'new vessel'[TRACTOR_NO] = vTractorCurrentRow && 'new vessel'[VoyageID] = vVoyageCurrentRow && 'new vessel'[New On Chassis Time] < vNewOnChassisCurrentRow )Let me know if that works for you.
If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
Best regards
Denis
Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic- Anonymous3 years agoNot applicable
selimovd I am sorry that I have left for a few days. And I am home just now.
Thanks for your alternatives, it works the exactly the same as what I expected. Though I still have no idea why EARLIER function doesn't be suggested (as I have gone through the articles and websites, they don't explicitly explain it well but only suggest using varaible is an alternatives), I might have recognized the root cause after understanding the principle of EARLER.
It creates an inner virtual table for every row. So my original DAX was actually creating a very large table to be mapped with which consumed a super big amount of memory.
It is my understanding but I don't really know if it is correct because.... from your alternatives, the difference between us was
1. Apply ALL() function to clear filters being applied
2. Discard EXCEPTALL() function and replace it by filtering (with VAR)
3. EARLIER() function is being replaced by creating VAR
The whole idea is the same, but would you like to explain a bit what makes yours different from mine?