PowerBIforSchools

One of the hallmarks of getting better at BI is writing more complex DAX in columns and measures so I want to share with you guys by far the most helpful thing I´ve learned DAXwise - variables. The concept is really simple - you give a name to a value or a table with some DAX and you can then refer to it by its name in a final calculation or another variable. We´ll do the syntax then I´ll give 5 reasons why this simple concept is actually awesome. Syntax VAR <name> = <expression> ...repeat for as many variables as you like RETURN <expression> e.g.  TwoPlusTwoEqualsFour = VAR a = 2 VAR b = 2 RETURN a+b 1. Troubleshooting We all know the feeling when you write a big string of code, hit return and then you get nothing, or every kid has the same number and you know you´ve messed up. Using variables allows you to check the various elements of the calculation without touching the other steps. For example, this is the formula for a...

I have mixed feelings about scatter plots. One the one hand you´re showing each individual kid which can be a useful corrective against the fact that taking averages can obscure interesting outliers and patterns. However, the strength of showing everybody is also a weakness as it´s very difficult to compare one scatter with another or to get an impression of how a year/class is doing overall because there are so many different pieces of information to process. It can also be hard to convey to teachers how to use them productively because (as we´ll see) a simplistic above the line good, below the line bad approach is misguided. There are also BI specific problems we need to address, but first lets look at the properties of the visual. My sample data is described in a previous post, my pbix is here and you can download it here. The Scatterplot controls Details: StudentID – that means each student gets their own dot. Legend: Result type – that means the results for each Re...

The other day someone asked me on twitter if I could do this in BI. This is a scatter plot of pupil attainment data. It has KS2 on the x axis, current/predicted/whatever on the y axis and a line showing some measure of national performance. His line comes from a different graph underneath the scatterplot and his issue is the axes don't quite line up - so he wants to know if I can sort it. The answer is not exactly, but the journey’s pretty interesting so I’m going to walk through what I did, even though I’ll admit at the start we won’t end up with the exact target visual. Get the Data You can download my pbix to follow along with here. I’ve used a dummy result set with a student table including KS2 Results (average KS2 is used so we only need one value per student). My results follow a pretty common format of Result Type, Subject, Year and Term. To get the national data I googled ‘transition matrices’ and got to here on gov.uk https://www.gov.uk/government/pu...