Query in Excel form Power BI dataset can not run because of memory

[KasperJ90]

Hi,
I use below query code in a Excel file linked to my Power BI dataset. The issue is I receive an error on memory.

EVALUATE FILTER(
SUMMARIZE(
'Sales Table', 'Sales Document'[Customer Code],
'Sales Table'[Amount],
"Total Sales", [Total Sales]),
'Sales Table'[Season] = "200" )

but I get this error:

The Sales table is 400.000 rows. Why can I not run this without an memory error? 

[cassidy]

SUMMARIZE is pretty expensive as I believe it needs to load the entire table into Memory.  You have 2 GB of Memory available to you and you are simply asking for too much.

Filtering further likely won't help either as it's more about your service capacity trying to load 400k Rows into Memory to peform the SUMMARIZE

You either need more Memory (Power BI Premium) or pull that data a different way, such as a Power BI connected Pivot Table where you drag in those fields with filters.

[KasperJ90]

Thank you for your reply @cassidy 

For another Power BI table I use Excel just with an evaluate formula like this:

EVALUATE 'Product Table'
This works fine and no memory issues with 200K rows and 68 columns. The reason why I am not using
EVALUATE 'Sales Table' is that my measures are not included from the Sales Table (or any other tables).
Can I somehow add the measures via the EVALUATE formula or use another formula to pull out the Sales Table? I can as you mention load the total dataset, but performance is too slow and complicated for users since they can see the whole model when they are working with pivot tables.

[cassidy]

It's not unreasonable that population your 200k Product Table works fine, it's potentially less Memory consuming.

I would try building the exact table with filters in a Power BI Service report and then Exporting it with Live Connection (a somewhat newer feature).  This will write a Query for you that may be more performant or at least you give you more ideas.

You can check what it wrote by editing the Query of the Excel table: