cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
qubit813
Post Patron
Post Patron

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)
5 REPLIES 5
qubit813
Post Patron
Post Patron

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
2019-2020-1-A2019-2020-11Aa

2019-2020

2019-2020-2-A2019-2020-22Ab2019-2020
2020-2021-1-A2020-2021-11Aa2020-2021
2020-2021-3-A2020-2021-33Ab2020-2021
2019-2020-1-B2019-2020-11Bx2019-2020
2019-2020-2-B2019-2020-22By2019-2020
2020-2021-1-B2020-2021-11Bx2020-2021
2020-2021-3-B2020-2021-33By2020-2021
 

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

@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.

@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 

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:
 page.png

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

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.