Showing results for 
Search instead for 
Did you mean: 
Post Partisan
Post Partisan

need help optimizing a measure that is resulting in >1 GB of memory

*This is going to be a long posting.  Because of the 20,000-character limit, I'm going to chop it up into several postings.
I am having trouble with the performance of a certain DAX measure.  Even though my dataset size is small (~20 [MB]), this measure is resulting in a memory size of 1+ [GB], which exceeds the shared capacity memory limit (i.e., 1 GB), thereby causing the visual to not render.  By inspection in DAX Studio, I have confirmed that the given measure is causing the memory explosion, but I don't know what about the measure is causing it.
My report is about employees' billable time percentage, i.e., utilization.  If an employee worked nothing but billable projects, then his utilization = 100 %.  If an employee worked both billable and non-billable projects, then his utilization is <100 %.
One complicating factor is that all employees will have a certain calendar type.  The cardinality of calendar type is low (about 10).  Each calendar type categorizes each week for each fiscal year.  The cardinality of week category is also low (about 10).
Employees can also be classified by an 'Employee Group'.  For example, some employees with [Calendar Type] = 'A' could be in [Employee Group] = 'one'; other employees with the same [Calendar Type] could be in [Employee Group] = 'two'.
My goal is to compute the average utilization and the standard deviation of average utilization (*note: not the standard deviation of the atomic-level data), per calendar type, per week category, across a desired set of employee groups and fiscal years.  These measures will appear in a table that has [Week Category] as a dimension.  Because the [Week Category] values are different per [Calendar Type], [Week Category] dynamically shows only those values that pertain to the selected [Calendar Type].  For example, with [Calendar Type] = 'A' selected and only fiscal year 2019-2020 selected (i.e., a 52-week FY), the table might show the following:
Week Category              Average Utilization            Std Dev of Average Utilization
a                                              70 %                                                 25 %
b                                              65 %                                                   0 %
I have 4 tables:
Dimension Employee (grain = employee)
Dimension Project (grain = project)
Dimension Week Calendar Type (grain = week + calendar type)
Fact Hours Worked (grain = employee + project + week-calendar type)
Post Partisan
Post Partisan

Here is sample data for each table:
Dimension Employee
Employee KeyEmployee NameEmployee GroupCalendar Type
1John DoeoneA
2Jane DoetwoB


Dimension Project

Project KeyProject NameProject Type
1some billable projectbillable
2some non-billable projectnon-billable


Dimension Week Calendar Type

FY-Week-Calendar Type KeyFY-Week KeyWeekCalendar TypeWeek CategoryFiscal Year



Can you share the PBIX file? Also, if you need more than 20,000 characters, that is going to be a long post. That is about 40 typed pages.


Just share the PBIX via OneDrive, Dropbox, etc. along with the specific measure you are having an issue with and someone will jump in.

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting


I think the 20k-character limit is being thrown erroneously, since a character count in Word is showing something much less than that.  I get this error message quite often.  Either way, I had to find a way around the error -- hence the several replies and picture for one of my data tables.


I don't have a file sharing option available to me.  My company does have OneDrive, but we have blocked all external sharing in our tenant.


I was able to get a Google Drive account, and I posted my pbix file. 

Fact Hours Worked

fact table.png


Here is my data model:
data model.png
*Note: In order to aggregate the fact table by the desired dimension attributes, those dimension attributes have to be replicated from the dimension tables to the fact table.

Here are my measure definitions:
Sum of Total Hours.pngSum of Billable Hours.pngAverage Utilization.pngStd Dev of Average Utilization.png

Here is what my page looks like for some slicer selections:

Again, the measure that is causing the memory explosion is the [Std Dev of Average Utilization] measure.  How can I optimize this measure to reduce the memory explosion?

Helpful resources

2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors