PowerBIforSchools

  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) My Data 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

This is the first of a few little posts I'll be doing on SQL as it seems like a fair few of you use it in complement with power BI. I'll be assuming a basic knowledge of SQL so familiarity with SELECT, UPDATE and DELETE statements, subqueries and inner and out joins. If you need to get up to speed with the basics of SQL there are free courses on udacity and a good syntax reference at https://www.w3schools.com/sql/ To kick us off let's consider a problem about Progress 8 estimates on a table with 3 columns, KS2, Estimate and SeasonYr : if they exist for the year of the results then I want to use the ones from the appropriate year, but I can't just join on year because my latest estimates are from 2019 but I have actual results from 2020. So I want to use the right year if it's there and the latest available if not. The first step is to create a view with the latest estimates, which you can do by joining the table to a subquery finding the latest year Create vi

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