PowerBIforSchools

This is a little post about a really simple change that I've found really helpful concerning tables/matrixes showing NaN and Infinity. What's up with this? This happens when you have a measure that equates to <a number> / nothing If your nothing is the number zero then BI shows infinity. More commonly though your nothing is...nothing. BLANK() in terms of DAX (null in SQL). That's when you get NaN which stands for Not a Number. Note that if your denominator is COUNTROWS and there are no rows in the filter context then DAX returns BLANK not 0. Solution The simple solution is to use the DIVIDE function which takes the numerator and denominator as its first two arguments. If the denominator is blank then by default it returns BLANK() (So crucially won't force a row to appear in a table). You can also add a third argument with an alternate result if the numerator can't be divided by the denominator. So now you don't find / in my measure code because DIVIDE is b...

In an earlier post   I explained how to get Covid-19 data from the Public Health England API. You can download my data monitor PBI filehere but you should refer to that post to explain how to create queries to get the specific local authority data you need for your school. What data is there and how should we monitor it? The PHE data has different indicators for different area types (they’re also subject to change so check here  for the latest info). At local authority level you can get new cases, cumulative cases (which is all since the outbreak began, not all currently ill) and cumulative cases per 100k of population. I think cumulative cases is irrelevant so I’m monitoring new cases in the past 7 days. Cumulative case rate is also irrelevant but useful for calculating the borough population. My tracker also monitors hospitalisation and death figures although these are only available at the national not the local authority or regional level. There obviously isn’t ...

Monitoring the spread of corona virus will be an additional challenge for educators this autumn on top of the usual challenges of making a successful start to the academic year. This post explains how to get Covid19 data from Public Health England (PHE) into BI via Power Query. In subsequent posts I’ll talk about what you can do with it in BI. If you know of other sources of Covid19 data (with a publicly accessible API) please let me know. The Data Source PHE publish data for six area types, overview (whole UK), nation (England, Scotland, Wales and Northern Ireland), Region, NHSRegion, Upper Tier Local Authority and Lower Tier Local Authority. I haven’t quite worked out the difference between Upper and Lower LA but Lower Tier seems to correspond with London Borough/Local Education Authority and is the most granular so that’s the level I’ll be focusing on. (There is a map here https://www.arcgis.com/apps/webappviewer/index.html?id=47574f7a6e454dc6a42c5f6912ed7076 with smaller area...

I've recently changed my approach to filtering table and matrix visuals and it's led to big performance improvements in the load times of these visuals so I want to share it with you guys. Basically what I've done is stopped in nearly all cases using measures to filter visuals and instead started writing my measures so that they return BLANK() instead of a result I want to filter out. I'm going to explain a bit about BLANK() first, then I'll give an example of how this works. Blank as a concept If you have a background with other database programs you'll probably be familiar with the idea that null - the absence of a result - is different from 0 or "" (an empty string). If you're background is mostly Excel then you should be aware that BI treats null (no value in a column) differently from "" which would denote a string with length 0. We don't need to go deep into the weeds about why this is and how null is treated in every case, ...

Hi guys, sorry for the slow pace of posts, I know that now is a great time to learn for lots of you – unfortunately I’ve been more busy not less. I’ll try and get back into the good stuff soon but in the meantime here’s a round up of some new (and newish) features of BI.  New Features Say What?  Microsoft have been really good up to now (July 2020) in rapidly improving Power BI with a new release most months. If you downloaded PBI desktop via the Microsoft store it should update automatically, if not check for updates. When you get a new release it’s always worth having a look in Options| Preview Features as most functionality additions start off being ‘opt in’ for a couple of months before they’re available generally. OK on to the good stuff:  Decomposition Tree   The decomposition tree is one of BI’s “AI” visuals along with Key Influencers and Q&A and while I’m not sure I buy that it’s a step change from a regular visual, it is pretty cool. ...

NOTES The data for this dashboard comes from the DfE's Compare School Performance site and Ofsted's summary of inspection grades (overall effectiveness) as at 31st August of the selected year. Note that figures are not published for all schools so the totals for LEAs/ all state funded schools differ slightly than those shown on the DfE's site. If you have any questions or suggestions please ask either below or on Twitter @BI4Schools

It’s important to monitor the performance of contextual groups like boys, disadvantaged pupils, those with SEND etc, but as a rule we don’t want to have to filter down to each one in turn, it’s better to view a summary and only dig deeper if there’s a particular problem.  However, you can’t simply throw all the factors into a table because all the groups overlap. My initial solution to this was to have lots of different tables on the page but that was hard to maintain and basically just looked a bit rubbish. Now what I do instead is to summarise the data into a separate ContextualData table. I’m going to do a two part post on this, where here in part 1 we make the table using internal data and then in part 2 we bring in national data as a comparison. To make life easier for me I’ve got all my student data contextual and attainment on one table (you can download my pbix here ) but as the attainment data is calculated by measures this technique will work just as well if...