AAB with 2 in Facilitating Subjects
I'm working on KS5 at the moment and an interesting measure from a DAX point of view is AAB with two in facilitating subjects (where facilitating subjects are just a subset of A Levels that the DfE has decided are important)
The data I'm using a really straight up data model with a students table and a results table. On my results table I have size (in a levels) a 1/0 flag to say if a result is a facilitating subject and I have a QualType field that says if it's an A Level, Academic, Applied General or Tech Level result. This info comes from a spreadsheet DfE publish called Qualifications counting towards 16 to 18 performance tables.
I merged these together in my data warehouse but if that's not possible I'd recommend doing it in Power Query rather than importing and joining in BI because qualifications are not uniquely identified by QANs so you can't create a one to many relationship. By contrast, if you import the spreadsheet in power query and merge it to your results query using the QAN you can easily add the info. Note that size = Guided Learning Hours / 360.
The guidance isn't crystal clear on this point but as best as I can tell kids count to this measure the first year they are entered for qualifications of at least 1 A level in size with a total size of at least 2 A-levels (ie two regular A2s or one double A Level). Assuming that's true we work out what year that is in a calculated column on the students table:
VAR Entries = FILTER(
VAR CohortYear = MINX(Entries,Results[SeasonYr])
Note that this cohort is different (and smaller) than the total number of students counting towards Average Points per Entry and Value Added, so rather than filtering them with a relationship I’m going to get the cohort number by looking at a year selector I have in a separate table (called SwitchAY) and using that inside calculate
VAR AY = SELECTEDVALUE(SwitchAY[Year])
VAR C = CALCULATE(COUNTROWS(Students),
The heat of the meat
So we have a way to work out our denominator now we just need to know the numerator. At its simplest, what a DAX measure does is reduce the information on a table to a single figure. Therefore the best approach to a new problem in DAX is to think first, how do I get the table that contains the information I want, second, what aggregations do I need to reduce that table to a single point and then third how I will I mess it up and end up with the wrong result.
I like to start off with parts one and two in my head rather than trial and error on the screen because I think that helps with the vital step three. Obviously the table that we’re going to need is the results of the students, reduced to a Yes/No for each student. Here are the edge cases *that I thought of* that could potentially throw me and be aware that by no means implies I thought of them all.
Say you have a kid with two As in non Fac subjects and two Bs in Fac subjects. They have AAB and they have two Fac subject results but they don’t have AAB with 2 in Facilitating. To guard against this I decided to consider only the students’ top three results using TOPN.
There’s a full explanation of TOPN in this post onAttainment 8 but it’s syntax is basically
TOPN(<N - number of records>,<table>,<field used to sort>,<sort order>)
The really crucial thing to know about TOPN though is that it doesn’t break ties. So if you did TOPN(3,Results,Points,desc) and you have a 100 A*s with 60 points then you would get a table with 100 results in as all those results have sufficient points to make it into the top 3. It’s more accurate to describe it as “TOP however many records qualify as being in the top N”.
Therefore in order for TOPN to be useful you need a unique index ranking all results by size. I use ROW_NUMBER() to do this in SQL but you can do it by adding an Index column in Power Query (I call it ResIndex which is how you’ll see it referred to in the formula below).
A little while after I proudly plugged this into BI I thought of another plausible edge case that could cause an inaccuracy – say you have a kid who has two As (one Fac one no) and two Bs (one Fac one no). Whether you count him as yes for the measure will depend on whether the facilitating subject B is included in his top 3. Therefore when sorting the results to determine the ResIndex you should sort by Points descending then by FacSub descending (assuming that 1 denotes a FacSub and 0 a non FacSub).
Once you can be sure that you have a student’s best three results and FacSubs are prioritised over non fac subs you need to determine which aggregations prove AAB with 2 fac subs have been achieved. I think you need three for this – sum of FacSub, sum of Points and min of points (because A*,A,C has the right total but not the right grades).
For the final measure I use CALCULATETABLE to get a table of the students in the measure for the selected year.
I then use ADDCOLUMNS to add to this table the three aggregations of the results table I need to know whether they qualify or not. Next I run SUMX over this table variable checking these three aggregations then finally divide the total by the number of rows in the students table (ie the cohort).
AAB with two Facilitating =
VAR AY = SELECTEDVALUE(SwitchAY[Year])
VAR Stds = CALCULATETABLE(Students,
Students[AL_Cohort_Yr] = AY)
VAR AABFtbl = ADDCOLUMNS(Stds,
VAR AABF = SUMX(AABFtbl,SWITCH(TRUE(),
VAR Cohort = COUNTROWS(Stds)
Further Explanation and Context Transition
To go through the formulas in the added columns (working from the inside out):
RELATEDTABLE is used to bring related records from the many side of a one to many relationship (as opposed to RELATED which brings a scalar value from the one side).
TOPN gets the top 3 results. Because the order is determined by the ResIndex which uniquely ranks every result it is guaranteed to return a maximum of 3 records.
SUMX iterates through those three or fewer records adding the values of Points or FacSub
MINX iterates through the three records and returns the lowest value of Points.
CALCULATE - I think it may be superfluous here and the formula should work without it (but I'm not 100% because it works as is so I didn't bother to check).
The reason why I, out of habit, included CALCULATE here is interesting - nearly always when using ADDCOLUMNS you use calculate to perform what's called context transition, which is where row context is transitioned into filter context.
Say you have the formula:
SUMMARIZE creates a table with one row per student and when you use ADDCOLUMNS the columns you add have row context (just like when you add a calculated column to a table). But aggregations like SUM, COUNT etc aren't affected by row context - so the formula above will sum the points of all the records of Results in the filter context, not just the points of that row's particular student.
If you change the last expression to CALCULATE(SUM(Results[Points]))) then context transition occurs, translating the row context (ie which kid we're talking about) into filter context. That makes the formula return the sum of the points of the right student which is far more useful.