Using BLANK() to Filter Tables and Matrices

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, there…

Power BI New Features

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. 

You find it on the Insert tab in the latest…

DFE Data


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

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: