Persistent Absentees

One of the challenges I've recently faced is how to get a measure to show persistent absentees (students with <90% attendance), that will respond to any time period on a date slicer.

 If you want to follow along with this example then you can access a sample data set or if you just want to visualise I will refer to two tables: 

 Students - a list of students
StudentAttendances - a list of attendance records, with one row per student per session that includes the mark meaning (P - present, E - Approved Educational Activity, A - Absent, U - Unauthorised, C - Cancelled).
Students is related to StudentAttendances with a one-to-many relationship.

 Step 1: Present and Possible

 My first step was to create two columns on the StudentAttendances table. 

 Present: = if(or(StudentAttendances[MarkMeaning]="P",StudentAttendances[MarkMeaning]="E"),1,0)

 Possible  = if(StudentAttendances[MarkMeaning]="C",0,1)

 Step 2: Attendance% measure

 Next I created a measure on the StudentAttenances table to calculate the percentage attendance

 Attendance% = sum(StudentAttendances[Present])/sum(StudentAttendances[Possible])

 Side Note: Columns increase the size the memory used, so this isn't the maximally efficient way of doing this. For a school sized db that probably won't matter so I prefer the simplicity of creating the columns separately. For a large MAT with millions of attendance records, it could be better to not make the columns and do the present possible calculation in the measure like this:

 Attendance%2 = sumx(StudentAttendances,if(or(StudentAttendances[MarkMeaning]="P",StudentAttendances[MarkMeaning]="E"),1,0))/sumx(StudentAttendances,if(StudentAttendances[MarkMeaning]="C",0,1))

 The (function)x functions in DAX essentially let you create temporary calculated columns in tables with measures.

 Step 3: Persistent Absentee Measure

 Persistent Absence is a condition of students, so for this we create a measure on the Students table, first to count the number of students whose attendance% is less than 90%

 PersistentAbs = countrows(filter(students,and(calculate(StudentAttendances[Attendance%])<.9,isnumber(calculate(StudentAttendances[Attendance%])))))

 Calculate in this measure does what BI calls 'preserve the row context'. That means that it works out the Attendance% figure for each row (ie each student) rather than for all the students combined. 
The IsNumber formula checks that attendance exists for the student - if their attendance percentage is a number then they have at least one possible session.

 Step 4: Persistent Absence Percentage

 Finally we divide our PersistenAbs student total by the number of students with attendance in this period (which could include students who are now off roll). The denominator is like the PersistentAbs count but without the condition that Attendance% is <90%.

 PersistantAbs% = [PersistentAbs]/countrows(filter(students,isnumber(calculate(StudentAttendances[Attendance%]))))

 Here are the figures from the sample data set

Comments

  1. Unknown7 May 2019 at 09:43

    Hi,

    Thanks for all your work on the blog and trying to demystify PowerBI. I am trying to follow along your example but can't seem to get past step three. When I enter the formula it tells me that "a single value for column 'Attendance%' cannot be determined .... etc.

    Presumably this is because there are multiple rows (even though they should all have the same attendance%). Can't work out how to get past this. Any guidance greatly appreciated!

    ReplyDelete
  2. Ed Cadwallader7 May 2019 at 23:41

    Hiya,

    That sounds to me like you're entering the formula as a column instead of a measure. Columns give us a single figure in the table to which they are added. Measures are much more flexible as they change according to context. E.g. if you create the persistent absentee measure and drag it on the canvas as a card it will give you the figure for the whole school, drag it into a table with students gender it will give you separate figures for boys and girls etc.

    ReplyDelete
  3. Max31 October 2022 at 01:27

    Thanks for your work on this. I've been wracking my brain for a way to get PA working as a measure and came up short. Your solution works perfectly!

    ReplyDelete

Post a Comment

Popular posts from this blog

Data modelling for a large MAT

Image
The wrong way When I first started developing in BI I worked on single schools and MAT demo systems with tiny numbers of students so my approach to data modelling was chuck data in however it comes then play with relationships until the configuration works. When I switched to developing for a large MAT (c40k kids in 50 schools) I found out the hard way the limitations of this approach-my measures and reports were unusably slow.  As well as the sheer volume of data I had problems because the MAT wanted a report on pupil demographics, attendance and exclusions with historical accuracy- ie not only seeing data for who's FSM, PP etc today but for any point/period in the past as well. This divides your pupil contextual factors into two types; unchanging, like ethnicity, EAL & KS2 prior attainment that don't change and so can stay on the students table, and changeable, like Pupil Premium eligibility, that can vary depending on the date range you're looking at.  My original
Read more

Subject - Class - Pupil Table

Image
One of the really powerful features of BI is being able to drill very quickly from the macro picture to the micro. To help schools do this with assessment results I've recently been working on this table showing subject, class and pupil level results (p2 of the report below). You can download the PBI file here , the class information looks like this  In a previous post I described concatenating studentid and subject to link classes, assessments and students, but that's not actually best practice - you can do it more simply with this setup I created the Subjects table using Dax (create table on the modelling tab). The formula is  Subjects = DISTINCT(AssessmentResults[Subject]). It gives a one column table with each distinct value of subject.  Note that here it is assumed that StudentClasses[Subject] isn't always the same AssessmentResults[Subject], as for example English Classes will often have English Literature Results. To handle t
Read more

Attainment 8 in BI - Part 1

Image
It’s been bugging me for a while that I didn’t know how to calculate A8 in BI, even though as a mostly Bromcom user it wasn’t a big deal as you can get the figures straight out of the MIS. That said, it was interesting solving all the steps along the way and even if you do get these figures out of your MIS or an Excel broadsheet I think you’ll find the exercise useful for building your BI skills. Source Data I’m going to assume source data in the form of a table of exam results with a student identifier, season, result date, exam code and result. You can download my source data  and/or the PBIX file . In a BI4Schools first we’re not going to import this data as is though, as we’ll be amending it using Power Query. Power Query (PQ) is the set of tools BI uses to get and manipulate data before placing it as a table in the BI model. PQ uses a language called M to do this – note that it is different to DAX, the language used within the BI model. To open Power Query click ‘Edit Qu
Read more