Forum Discussion

ak77's avatar
ak77
Post Patron
3 years ago

Not Enough memory Error

Hi All,

 

Need a help. I am try to test a large data(more than 2 million records) set with my power bi desktop. The destop after sometime gives the below Not Enough memory Error. The error mentiones the issue with Memory but is there a way to solve this issue . please help

 

The data load from source to bi is faster. The time is consumed during summarize execution.i am using the below SUMMARIZECOLUMNS logic  to display table in the format i need. is this causing the issue? most of the colums are measures  in SUMMARIZECOLUMNS

 

Can there be a better way to use this SUMMARIZECOLUMNS. Please help 

 

 

Report_View_MS = SUMMARIZECOLUMNS (
        Client_Returns_Data[sequence],
        'Date Table'[LastDayOfMonth],
        Client_Returns_Data[portfolio_code],
        Client_Returns_Data[Asset/Sub-Class Benchmark],Client_Returns_Data[Sequence Asset/Sub-Class Benchmark],
        "QTD", [QTD_MS_Change],"YTD",[YTD_MS_Change],"1 year", [1 Year_MS_Change],"2 year", [2 Year_MS_Change],"3 year", [3 Year_MS_Change],"4 year",
        [4 Year_MS_Change],"5 year",[5 Year_MS_Change] ,"10 year", [10 Year_MS_Change],"Qtr", [Qtr_MS_Change],"Qtr(T-1)", [Qtr(T-1)_MS_Change],"Qtr(T-2)", [Qtr(T-2)_MS_Change],"Qtr(T-3)",[Qtr(T-3)_MS_Change],"Calender(T-1)", [Calender (T-1) Year_MS_Change],"Calender(T-2)",[Calender (T-2) Year_MS_Change]
    ,"Calender(T-3)",[Calender (T-3) Year_MS_Change])
   
Need Help
Show and Tell

10 Replies

  • parry2k's avatar
    parry2k
    Super User
    3 years ago

    ak77 what are the measures you are using in the summarizecolumns, and more of it, why you are doing it?

    • ak77's avatar
      ak77
      Post Patron
      3 years ago

      parry2k , these are time series measures (QTD,YTD ,rolling returns etc). I am doing this as i need a report view and was achieved using summarize columns. 

       

      Will this work in power bi service if i publish and do a incremental refresh? basically i am trying to pull 10 years of data( 2+ million records)  in desktop plus the summarize columns and this is creating memory issue. The summarize columns works fine for small data sets( 2years data). 

       

      please let me know

  • parry2k's avatar
    parry2k
    Super User
    3 years ago

    ak77 yes incremental refresh will work, but I'm still not able to get my head around the use case of the new table you are creating using summarizecolumns.

    • ak77's avatar
      ak77
      Post Patron
      3 years ago

      parry2k , Thanks for reply.. The purpose of summarize columns was to achieve a requirement of calculating  the rowwise difference of previous 2 rows and display them in the 3rd row in the power bi layer using matrix viz.

       

      we tried lot of other options and lastly settled for using summarize columns. sample is below . please help if u have any other options to resolve this requirement or optimize  summarize columns logic

       

       

  • sergej_og's avatar
    sergej_og
    Super User
    3 years ago

    Hey ak77 ,
    you may be right with your assumption. It looks your SUMMARIZECOLUMNS has to do a lot of work.
    But you can best answer the question yourself.
    Comment out your SUMMARIZECOLUMNS and let say replace just with 1. Just to avoid a break in your calculations.
    Do you still get memory error message?

    Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi,

    Instead of using the Summarized column try to use add column it is more efficient than the Summarized column.
    The other thing you can try if you are publishing from one environment to another environment like
    Dev--->> UAT---> Prod.
    then just publish the lower environment dataset in the upper environment as it is. and change the parameter and refresh from the service.
    It will work.
    Make sure your data should be in sync

  • parry2k's avatar
    parry2k
    Super User
    3 years ago

    ak77 why you cannot use these measures directly in a matrix visual without creating a new table?

    • ak77's avatar
      ak77
      Post Patron
      3 years ago

      The difference logic didnt work when tried. so had to go for summarize route

  • parry2k's avatar
    parry2k
    Super User
    3 years ago

    ak77 I think the main issue is your DAX measures, I don't see any need to create an extra table for difference logic. Also what you are referring to as a different logic? I would recommend rather than sharing your DAX issue, share sample data with what you want to achieve, and you will get a more precise solution.