Not enough memory error when using a calculate function.

[maxbradley]

I'm trying to calculate the percentile of a value, so dividing a count of the values lower than the row value by a total count of the values. However, the dataset has multiple years, and I want the percentiles to be calculated only within their own year. The code I'm using is;

AttendancePercentile =

VAR Position =

[Attendance]

VAR Acayear =

[time_period]

Return

CALCULATE(COUNTROWS('1_absence_3term_geography'),

FILTER('1_absence_3term_geography',

[Attendance]<=Position &&

[time_period]=Acayear

)

)

And this is producing the error;

<pi>There's not enough memory to complete this operation. Please try again later when there may be more memory available.</pi>

The table has 22 columns and 23,717 rows, so not small but by no means the largest I've worked with, which makes me suspect that there is some sort of loop in the DAX code causing the computing process to drop down a rabbit hole. Do you see anything wrong with the above formula, and if not, what further steps can I take to diagnose my memory issue?

Thanks in advance

[Anonymous]

Hi  @maxbradley ,

You can try the following methods:

Could have to do with how you have you data model created in Power BI.

1. You can use Dax Studio to check, use the following link to do it, Find your longest running queries and analyze them.

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=6cd37af6-...

2. Also related to the format of the datetime:

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=aa5e34b8-...

3. Try disabling ‘Auto date/time’ under Options > Data Load > Time intelligence.

Or

Another way is to go to "Data Cache Management Options" and clear the cache or increase the "Max Allowed" number.

This is the related document, you can view this content:

https://community.powerbi.com/t5/Desktop/There-s-Not-Enough-Memory-To-Complete-This-Operation/td-p/3...

https://blog.magnetismsolutions.com/blog/colinmaitland/2019/02/05/power-bi-desktop-memory-issue-reso...

https://blog.magnetismsolutions.com/blog/colinmaitland/2019/02/05/power-bi-desktop-memory-issue-reso...

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[maxbradley · Accepted solution]

Thanks a lot for your help.

Just for anyone who has the same issue, the solution that worked for me is a little embarassing; Update PBI.

I'll also follow your advice to see if I can streamline the process as well.

Best,

[SpartaBI]

@maxbradley is that a calculated column. If yes, then try this instead:

AttendancePercentile =
VAR Position = [Attendance]
VAR Acayear = [time_period]
RETURN
    COUNTROWS (
    	FILTER (
        	'1_absence_3term_geography',
        	[Attendance] <= Position
            	&& [time_period] = Acayear
    	)
    )

Showcase Report – Contoso By SpartaBI