topic Re: Memory error: Memory Allocation failure . Try simplifying or reducing the number of queries. in DAX Commands and Tips

Memory error: Memory Allocation failure . Try simplifying or reducing the number of queries.

analytics_uk — Wed, 28 Apr 2021 11:53:34 GMT

Hi,

I get the above message when refreshing on Power BI Online.

I have isolated the problem and know that it is literally related to the one calculated column below:

_Next Order Date = CALCULATE(MIN(Order_Header[Date]),FILTER(Order_Header,Order_Header[Date] > EARLIER ( Order_Header[Date] ) && Order_Header[Customer_Email] = EARLIER ( Order_Header[Customer_Email] )))

I need this for a host of different measures. It just gives the next order date for a particular customer.

Is there a way to change the setting on power bi online to accomodate this before I look at ways of changing the DAX?

Many thanks in advance

Re: Memory error: Memory Allocation failure . Try simplifying or reducing the number of queries.

amitchandak — Wed, 28 Apr 2021 12:03:06 GMT

analytics_uk , what is the source data? How many rows

I am assuming you are getting this on power bi service, not desktop.

If Desktop, check

https://windowsreport.com/memory-error-allocation-failure/

Re: Memory error: Memory Allocation failure . Try simplifying or reducing the number of queries.

analytics_uk — Wed, 28 Apr 2021 12:09:34 GMT

Hi amitchandak

there are some 200k rows on the table where that calculated column lies.

It comes from a SQL query that i wrote, if that's what you mean by source data?

And yes, I get the error on power bi service and not desktop

Re: Memory error: Memory Allocation failure . Try simplifying or reducing the number of queries.

amitchandak — Wed, 28 Apr 2021 12:44:47 GMT

analytics_uk , Please also log an issue - https://community.powerbi.com/t5/Issues/idb-p/Issues

this does seem like a big number.

Re: Memory error: Memory Allocation failure . Try simplifying or reducing the number of queries.

Anonymous — Wed, 28 Apr 2021 21:29:49 GMT

Hi analytics_uk

First off, one should refrain from calculating columns via DAX (too many reasons to state). It should be done in the source or in Power Query, where such calculations truly belong and where they'll be fast. Second, one should never use CALCULATE in calculated columns on big fact tables. CALCULATE executes context transition and this is always very costly. If you pair it with the number of rows that this needs to be calculated for in a fact table... well, you get the gist. It'll almost always throw a memory-overflow error.

Here's a version that does not use CALCULATE, so there's a chance it'll work. But, as I said, you should ideally calculate this outside DAX for speed and memory efficiency reasons.

[_Next Order Date] = var CurrentDate = Order_Header[Date] var CurrentEmail = Order_Header[Customer_Email] var Result = MINX( FILTER( Order_Header, Order_Header[Date] > CurrentDate && Order_Header[Customer_Email] = CurrentEmail ), Order_Header[Date] ) return Result