Insufficient memory

[Joswin_100]

Good afternoon,

I ask for your help,

I have the following error:

"There is not enough memory to complete this operation."

This error comes out when integrating the following function into a table and selecting all products:

Purchase $-SUMX(SO,[Quantity_SO]*[Purchase_Cost_TOPN]) // the goal is to get a purchase amount per product. The units sold by the purchase price (average of the last 5 purchases).

Quantity sold (Quantity_SO) - SUM(SO[ITEM_COUNT]) //sum of parts sold
Average purchase price last 5 purchases (Purchase_Cost_TOPN)-IF(HASONEVALUE(Items[ITEM_ID]),AVERAGEX(CALCULATETABLE(TOPN(5,PO,PO[CREATE_DATE]),PO[ITEM_UNIT_PRICE]&>0,FILTER(ALL('Dim_Calendar DAX'[Date]),'Dim_Calendar DAX'[Date]<-MAX('Dim_Calendar DAX'[Date]))),PO[ITEM_UNIT_PRICE]))

My model is integrated with 4 tables: SO(sales), Items, PO(purchases), Calendar (dates) /annex image of my model

The granularity level of my OS and PO tables is per day.

Tables have the following rows: items (73774 rows) / OS (3,248,016 rows) / PO(561,165 rows)

The actions I have taken to avoid this error are:

-Delete columns that are not needed

-Clear cache in options>data load> Clear cache

-Increase the maximum allowed (MD) to 16000 in options>data load> maximum allowed

I'm using direct query to bring information from a netsuite odbc (ERP)

But I'm still with the same mistake 😞

[v-diye-msft · Accepted solution]

Hi @Joswin_100 

Please kindly refer to the blog to fix the issue:

https://thegeekpage.com/fixed-there-is-not-enough-memory-to-complete-this-operation/

Besides,To debug this do the following:

1. Download and install Dax Studio http://daxstudio.org/
2. In you Power BI report create a blank report page and save the desktop file with this page open/selected
3. Close All Power BI desktop sessions
4. Start Power BI desktop again and open only this report LEAVE IT on the blank page!
5. Start DAX Studio and connect it to the Power BI Desktop file you have issues
   

   

6. In DAX Studio click the ALL Queries button
   

   

7. Wait until output windows shows
   

   

8. Now go to Power BI and start doing what you do while you are getting the memory error...
9. When you have it return to DAX Studio and see what is on the All Queries page of your query trace…
   

   

10. Find your longest running queries and analyze them…

[Joswin_100]

But when filtering a product, the function runs well (image annex)

In the salt table, the "buy $" column makes me the multiplication line by liea and the total makes me the sum of all the rows.

The buy order table, I leave it comp reference, basically in this table I get the average price of the last purchases