Forum Discussion
Insufficient memory
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 š
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:
- Download and install Dax Studio http://daxstudio.org/
- In you Power BI report create a blank report page and save the desktop file with this page open/selected
- Close All Power BI desktop sessions
- Start Power BI desktop again and open only this report LEAVE IT on the blank page!
- Start DAX Studio and connect it to the Power BI Desktop file you have issues
- In DAX Studio click the ALL Queries button
- Wait until output windows shows
- Now go to Power BI and start doing what you do while you are getting the memory error...
- When you have it return to DAX Studio and see what is on the All Queries page of your query traceā¦
- Find your longest running queries and analyze themā¦
2 Replies
- Joswin_100Frequent Visitor
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
- v-diye-msftCommunity Support
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:
- Download and install Dax Studio http://daxstudio.org/
- In you Power BI report create a blank report page and save the desktop file with this page open/selected
- Close All Power BI desktop sessions
- Start Power BI desktop again and open only this report LEAVE IT on the blank page!
- Start DAX Studio and connect it to the Power BI Desktop file you have issues
- In DAX Studio click the ALL Queries button
- Wait until output windows shows
- Now go to Power BI and start doing what you do while you are getting the memory error...
- When you have it return to DAX Studio and see what is on the All Queries page of your query traceā¦
- Find your longest running queries and analyze themā¦