Historical Memberships 2

In a previous post I covered how to show historical membership dates, but that referred specifically to the Bromcom MAT Vision dataset, and I’ve since found out a more elegant way of writing the measures, hat tip @JonnySBristol so I’ll recap briefly in the first section of this post, then cover some new techniques in sections 2 and 3.

1 Calculate historical memberships from an enrolments table.

My sample data set is structured as a students table with ID, name and gender and an enrolments table with studentid, StartDate and EndDate. This is better than having date of entry/leaving on the students table because it can accurately record a student leaving, then rejoining school.

As well as the student and enrolments table we create a DateRef table, via modelling New Table (more details in this in post 1).

Table from DAX

DateRef = CALENDAR(“01/01/2016”,TODAY())

Measures:

MaxDate = MAX(DateRef[Date])

MinDate = MIN(DateRef[Date])

We don’t need to relate DateRef (although we can, and I usually directly link it to my attendance table). All we need are MaxDate and MinDate which we’ll dynamically set using a date slider on the field DateRef[Date].

Historical Memberships

Once you have this set up you can write a measure for counting students who were on roll between your two dates.

StudentsOnRoll = CALCULATE(COUNTROWS(Students),

FILTER(Enrolments,AND(Enrolments[StartDate]<=[MaxDate],OR(Enrolments[EndDate]>=[MinDate],ISBLANK(Enrolments[EndDate])))))

CALCULATE allows us to perform an operation within the context of additional filters we supply (note that unless you say otherwise filters that exist from your report/page/slicers/row context will also apply).

Crucially for our purposes here, those filters can be applied to related tables and passed the table we are calculating. So our filter for enrolments that fall within our date range is passed to the students to whom those enrolments belong.

Single Date Filter

If you’re not interested in a range but only students on roll on a particular date, then change your slicer type to Before and use MaxDate in place of MinDate in the measure.

2 Calculating PP Eligibility

Pupil Premium  Eligibility (or any other changeable contextual factor) can be worked out in exactly the same way provided you have a table with studentids, start and end dates. We can use our existing on roll measure as the base (so we don’t have to type out the on roll filter again). Hence:

PPStudents = CALCULATE([StudentsOnRoll],

FILTER(PPDates,AND(PPDates[StartDate]<=[MaxDate],OR(PPDates[EndDate]>=[MinDate],ISBLANK(PPDates[EndDate]))))

)

Now moving the date slicer will show in this measure a count of students who were on roll and eligible for the PP.

If you want to see who the kids are then just create a table with the data you want from Students and add the PPStudents measure – by default it will only show those where the measure is not blank (ie PP = true).

Calculating Non PP Eligibility

How about the reverse case – students who are not eligible for the PP at a particular point in time. We can work this out using a couple of new formulas CALCULATETABLE – which works just CALCULATE but instead of returning a single value from a filter context it returns a table. And EXCEPT. Except takes two tables as arguments and gives you all the rows from the first (‘left’) table that don’t appear on the second (‘right’) table.

NotPPStudents =

CALCULATE([StudentsOnRoll],

EXCEPT(CALCULATETABLE(Students,FILTER(Enrolments,AND(Enrolments[StartDate]<=[MaxDate],OR(Enrolments[EndDate]>=[MinDate],ISBLANK(Enrolments[EndDate]))))),

CALCULATETABLE(Students,FILTER(PPDates,AND(PPDates[StartDate]<=[MaxDate],OR(PPDates[EndDate]>=PPDates[StartDate],ISBLANK(PPDates[EndDate]))))))

)

3 Date Sensitive Filtering

This is all fine if we know in advance what data we want to show – but what about slicers to allow our users to filter on date sensitive factors? You might think we could create calculated columns using our measures and slice on them but that won’t work because calculated columns are worked out without reference to slicer values – so they wouldn’t respond dynamically to our Min and Max date values.

There is a fix for this but, as we’ll see, it won’t let you go wild with the number of slicers you can place. Basically what we’re going to do is creating a floating table a bit like DateRef and refer to the SELECTEDVALUE from that in measure logic.

Create a table from the modelling tab:

PPSwitch = DATATABLE("PP",STRING,

            {

                {"Y"},

                {"N"}

            }

)

That’s a dead simple, 1 column table with possible values Y or N. We don’t need to relate it to anything, just stick it on the canvas in a slicer. The function SELECTEDVALUE returns the value from a column if it is filtered to a single value, otherwise it returns the alternative result.

So the formula SELECTEDVALUE(PPSwitch[PP],”All”) will return Y/N if we select them in the slicer, if neither are selected it will return All.

We can then create a measure:

SelectableStds = SWITCH(SELECTEDVALUE(PPSwitch[PP],"ALL"),"Y",[PPStudents],"N",[NotPPStudents],"All",[StudentsOnRoll])

The logic is pretty simple with only three outcomes, but adding more will obviously increase exponentially the number of conditions/measures you would need (e.g. having Y/N/All for SEN would mean building in IF statements to identify 9 separate outcomes. Hence the impracticality of having multiple slicers with this method.

Here’s the file