Contextual Groups Table

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 you have (as you pro…

5 Reasons to use Variables in your DAX Code

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.
VAR <name> = <expression> ...repeat for as many variables as you like
RETURN <expression>
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 measure from my last post on scatters: