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

[Anonymous]

*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)

[Anonymous]

Here is sample data for each table:

Dimension Employee

Employee Key	Employee Name	Employee Group	Calendar Type
1	John Doe	one	A
2	Jane Doe	two	B

Dimension Project

Project Key	Project Name	Project Type
1	some billable project	billable
2	some non-billable project	non-billable

Dimension Week Calendar Type

FY-Week-Calendar Type Key	FY-Week Key	Week	Calendar Type	Week Category	Fiscal Year
2019-2020-1-A	2019-2020-1	1	A	a	2019-2020
2019-2020-2-A	2019-2020-2	2	A	b	2019-2020
2020-2021-1-A	2020-2021-1	1	A	a	2020-2021
2020-2021-3-A	2020-2021-3	3	A	b	2020-2021
2019-2020-1-B	2019-2020-1	1	B	x	2019-2020
2019-2020-2-B	2019-2020-2	2	B	y	2019-2020
2020-2021-1-B	2020-2021-1	1	B	x	2020-2021
2020-2021-3-B	2020-2021-3	3	B	y	2020-2021

[edhans]

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.

[Anonymous]

@edhans 

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.

[Anonymous]

@edhans 

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

https://drive.google.com/file/d/1orQx8T-5MzPxDlDLqx0KG2kG3uS5tLf6/view?usp=sharing 

[Anonymous]

Fact Hours Worked

Here is my data model:

 
*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:

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?