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?
