Update: Check out the comments for some more tips on using DAX measures.
DAX is the language behind Power BI. If you want to get the most out of Power BI sooner or later you’ll need to write some DAX code.
The best DAX reference is Microsoft’s DAX reference website. But one of the main problems for schools new to using DAX is that most of the examples are related to accounting and financial measures, rather than grades.
Here’s some useful DAX measures for schools using Power BI. I’ll add to add to it when I can. If you have any useful measures, let me know via the comments below and I’ll add them to the list.
In the following examples, the source table is called ‘Tracking Grades’ and it contains columns called ‘Grade’ and ‘Grade value’
DAX to calculate the number of grades 4 or higher:
Grade 4+ = SUMX('Tracking Grades',IF('Tracking Grades'[Grade]>=4,1,0)/COUNTROWS('Tracking Grades'))
DAX to calculate the number of grades 5 or higher:
Grade 5+ = SUMX('Tracking Grades',IF('Tracking Grades'[Grade]>=5,1,0)/COUNTROWS('Tracking Grades'))
DAX formula to return the highest and lowest grades in the current filter:
Highest Grade = MAX('Tracking Grades'[Grade value])
Lowest Grade = MIN('Tracking Grades'[Grade value])
Some DAX to calculate average grades:
Average Grade = AVERAGE('Tracking Grades'[Grade])
The CALCULATE function below creates a new filter context, irrespective of any existing filters. CALCULATE is the only function that can change the entire filter context.
Average Grade Boys = CALCULATE ([Average Grade],PupilData[Gender]="M")
Average Grade SEN K = CALCULATE([Average Grade],PupilData[SEN]="K")
The following DAX examples work on a table called ‘PupilData’ that contains a column called ‘Gender’
Count the number of girls
The FILTER function returns a shorter table that is a subset of the original table. The COUNTROWS function does what it says and return the number of rows in the specified table or expression that returns a table.
Count of Girls = COUNTROWS(FILTER((PupilData),PupilData[Gender] = "F"))
Percentage of girls
Percentage Girls = COUNTROWS(FILTER(ALL(PupilData),PupilData[Gender] = "F"))/COUNTROWS(ALL(PupilData))
To convert an old GCSE points score into a fine grade
For example, schools in Wales use A*-G grades where a C grade is worth 40 points. It’s easy enough to calculate the average points for a subject, but how to convert (say) an average of 42.01 points to a fine grade of C+? This measure will do it for you:
AverageFineGrade = SWITCH( TRUE(), AVERAGE(Ks4Gcse[GCSEPointsScore]) >= 44, "B-", AVERAGE(Ks4Gcse[GCSEPointsScore]) >= 42, "C+", AVERAGE(Ks4Gcse[GCSEPointsScore]) >= 40, "C", AVERAGE(Ks4Gcse[GCSEPointsScore]) >= 38, "C-", AVERAGE(Ks4Gcse[GCSEPointsScore]) >= 36, "D+" )
And here’s how you can use this measure in a table visualisation:
If you have a useful DAX measure, please let me know in the comments below.