Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Not enough memory to complete this operation

I have loaded a 4MB excel into PBI (which concludes about 66,000 rows of data). And then I created a few calculated columns, and one of them got an error below:

 

There's not enough memory to complete this operation. Please try again later when there may be more memory available.

 

I searched online and tried some suggestions, for example, 

  • Turned off time intelligence
  • Increased data cache
  • Cleared cache
  • Disabled data preview to download in the background

However, all trials above went in vain. 

 

Some threads suggested it might be due to the poorly written DAX format.

I don't know whether my DAX is bad or not because it is quite simple. Below is the DAX having such "memory issue":

 

MaxLastHotTime = 
    CALCULATE(
            MAX('new vessel'[New Off Chassis Time]),
            ALLEXCEPT('new vessel', 'new vessel'[TRACTOR_NO], 'new vessel'[VoyageID]),
            'new vessel'[New On Chassis Time] < EARLIER('new vessel'[New On Chassis Time])
    )

 

 

I had a trial to take away the EARLIER() function. It works completely fine.

I had a trial to minimize the data file (as the excel is actually a combined log, having one month amount log being put together). It works completely fine as well.

 

Therefore, I know that the root cause is due to the EARLIER() function. 

 

Can anyone help solve this issue?

 

For Your Information, it is the specification of my laptop:

 

Memory: 16GB
CPU: Intel i5 7th Gen

 

 

Need Help
Tips and Tricks
  • selimovd's avatar
    selimovd
    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

13 Replies

  • aviral's avatar
    aviral
    Advocate IV
    2 years ago

    I was able to solve this error by increasing the Maximum allowed cache to 64000 MB. 

     

    "File"-->"Options and Settings"--> "Options"--> "Global-Data Load"--> "Data Cache Management Options"

     

    Also, ensure that you either have enough RAM or increase the Virtual Memory to the same size. 

     

     

    • Si_7777's avatar
      Si_7777
      Advocate II
      5 months ago

      Doesnt work for me the OK button is greyed out

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    3 years ago

    Hey Anonymous ,

     

    how big is the size of your data model?

    Can you post a picture of the metrics of the model or even better export them with DAX Studio?

     

    Does this happen on your Desktop or in the service?

     

    Best regards

    Denis

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks, selimovd

       

      After loading into the PBI, it is about 4MB. Sorry that I don't have a DAX Studio currently. It will be good if it can be solved without touching DAX Studio.

      I am using PBI Desktop.

      Below is the model and their relationship.

       

       

      • selimovd's avatar
        selimovd
        Most Valuable Professional
        3 years ago

        Hey Anonymous ,

         

        that is very strange, 60K rows and a 4MB file size and you get a out of memory exception.

        What is your data source? Do you do any crazy transformation in the file? Is it maybe possible to share your PBIX file?

         

        Did you deavtivate the auto date time?

         

        Best regards

        Denis

  • Hanitraholy's avatar
    Hanitraholy
    Regular Visitor
    3 years ago

    Hello I have the same issue about the memory to complete the operation I changed all earlier formula as suggested but the issue remains.

    I have also used maxx in DAX to calculate approximate value: 

    Cost = maxx(filter('COST','COST'[KEY]='TABLE'[key price]&&'TABLE'[total weight]>='COST'[min range ]),'COST'[CPK])
    Thanks for your help