Forum Discussion

analytics_uk's avatar
analytics_uk
Helper I
5 years ago

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

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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