Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have a series of measures that then get put into a 10*6 matrix that is causing it to return an 'insufficient memory' error. I've put down the calculation steps below, can my DAX be significantly optimised or am I simply asking too much?
For context I work in education. The below measure refers to the Present/EA % measure, which is a count of all present and EA attendance marks divided by the total number of marks. There are 190 school days a year, with 2 register sessions a day and my organisation has ~5000 students, so this is a count over 1.9M rows. A number of other filters are then applied to remove students that aren't counted towards a schools attendance;
Present/EA % DFESPEC =
//The first day in the selected AcaYear
var FirstAcaDay= CALCULATE(
MIN(TermTimes[Date]),
REMOVEFILTERS(
TermTimes[Term]
)
)
//August 31 16 years prior to the selected Acayear, Any student born on or before this day will be 16 in that Acayear
var BD16= DATE(YEAR(FirstAcaDay)-16,8,31)
//August 31 5 years prior to the selected Acayear, Any student born on or before this day will be 5 in that Acayear
var BD5= DATE(YEAR(FirstAcaDay)-5,8,31)
//First date of this years HT6
var HT6First= CALCULATE(
MIN(TermTimes[Date]),
REMOVEFILTERS(TermTimes[Term],TermTimes[AcaWeek],TermTimes[Date]),
TermTimes[Half Term]=6
)
Var PEApt= CALCULATE(
[Present/EA %],
FILTER(
CombinedSessionAttendance,
NOT AND(
RELATED(TermTimes[Half Term])=6,
RELATED(YearGroups[CollectionName])="11"
)
),
Enrolments[EnrolmentState]<>"S",
SchoolStudentId[DateOfBirth]>BD16,
SchoolStudentId[DateOfBirth]<BD5,
SchoolStudentId[DateOfEntry]<HT6First
)
RETURN
PEApt
There are then two measures, persistent and severe absence. A persistently absent student has attendance=<90%, and severely <=50%. This measure is the percentage of all students that fall into these groups, retaining the above filters;
PAcount DFESPEC2 =
var PACntTbl=
FILTER(
SUMMARIZECOLUMNS(
CombinedSessionAttendance[SchoolStudentId],
"Present/EA % DFESPEC", [Present/EA % DFESPEC]
)
,[Present/EA % DFESPEC] <= 0.9
)
return
COUNTROWS(PACntTbl)
StdCount DFESPEC =
//The first day in the selected AcaYear
var FirstAcaDay= CALCULATE(
MIN(TermTimes[Date]),
REMOVEFILTERS(
TermTimes[Term],TermTimes[Date], TermTimes[Half Term]
)
)
//August 31 16 years prior to the selected Acayear, Any student born on or before this day will be 16 in that Acayear
var BD16= DATE(YEAR(FirstAcaDay)-16,8,31)
//August 31 5 years prior to the selected Acayear, Any student born on or before this day will be 5 in that Acayear
var BD5= DATE(YEAR(FirstAcaDay)-5,8,31)
//First date of this years HT6
var HT6First= CALCULATE(
MIN(TermTimes[Date]),
REMOVEFILTERS(TermTimes[Term], TermTimes[AcaWeek], TermTimes[Date]),
TermTimes[Half Term]=6
)
Var StdCount= CALCULATE(
DISTINCTCOUNT(CombinedSessionAttendance[SchoolStudentId]),
FILTER(
CombinedSessionAttendance,
AND(
[PossibleNoCancelMissing]<>0,
NOT AND(
RELATED(TermTimes[Half Term])=6,
RELATED(YearGroups[CollectionName])="11"
)
)
),
Enrolments[EnrolmentState]<>"S",
SchoolStudentId[DateOfBirth]>BD16,
SchoolStudentId[DateOfBirth]<BD5,
SchoolStudentId[DateOfEntry]<HT6First
)
RETURN
StdCount
The persistent and severe absence measures then go into two 6*10 matrices, so these calculations must be run 120 times. I realise this is a lot to ask. Is there a way I can slim down the measures without losing the filtering?
Solved! Go to Solution.
Thanks for the recommendation. I tried DAX Studio but there wasn't an obvious way to measure measures (?). I read elsewhere that the FILTER function was a problem, so I've rewritten the measures to avoid this, and that's made a huge difference. The visuals now run quickly.
Hi @max_bradley if possible you could add columns in your data source do some of the heavy lifting for you rather than calculating everything in DAX, eg a 1 or 0 to indicate BD16 and BD5 against student names.
Thanks for the suggestion but they would need to be dynamic according to the dates selected. A student who is over 16 one year might have been 15 at the beginning of the previous year and thus eligible for inclusion.
Thanks for the recommendation. I tried DAX Studio but there wasn't an obvious way to measure measures (?). I read elsewhere that the FILTER function was a problem, so I've rewritten the measures to avoid this, and that's made a huge difference. The visuals now run quickly.
Hi @max_bradley ,
Regarding your question, optimizing dax is not an easy task. You can first try to set additional virtual memory size according to this article and see if it works.
There Is Not Enough Memory To Complete This Operation (thegeekpage.com)
If this still doesn't work, we recommend that you download appropriate analysis software, such as Dax Studio, to analyze which queries take the longest to run.
Best Regards,
Wenbin Zhou
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
91 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
131 | |
110 | |
96 | |
70 | |
67 |