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.
The best DAX reference is dax.guide, not Microsoft’s DAX reference website š
The way the [Grade 4+] and [Grade 5+] measures are written can be improved.
First, the measures don’t actually give you numbers — they give you percentages. Second, there is repetition in the code. You can introduce the following measure:
Number of Grades = COUNTROWS ( ‘Tracking Grades’ )
Then you can re-write the measures in the following way:
Grade 4+ = CALCULATE ( [Number of Grades], ‘Tracking Grades'[Grade] >= 4 ) / [Number of Grades]
Grade 5+ = CALCULATE ( [Number of Grades], ‘Tracking Grades'[Grade] >= 5 ) / [Number of Grades]
The [Percentage Girls] measure can also be improved:
Percentage Girls = [Count of Girls]/COUNTROWS(ALL(PupilData))
It can be further improved by introducing another measure that counts all pupils — similar to what I did with [Number of Grades] above.
DAX measures are very much reusable! š
Thanks DAX fan – I’ll update the main article later with your suggestions! David.
I worked in a school and developed systems for 20 Years and I have been a DAX Programmer for 5 years. If I can offer any advice – it would be to not learn Dax or Power BI piece meal. There are important concepts that you need to know and understand.
Try and get your school to pay for training from one of these people. It will be the best money they have ever spent and will save you a lot of time.
Sam McKay – Enterprise DNA
Matt Allington – Exceleratorbi
Marco Russo – SQLBI – This is very technical and I would use one of the other 2 first but they do have a very good dashboard course.
You need to learn and understand Filter and Row Context, Context Transition and Dimension Modeling it you will run into issues at some point that you do not understand.
Hope this helps
Chris
Thanks Chris – I can certainly recommend the SQLBI website – Marco Russo also does a very good (and free) guide to DAX measures. The other names are new to me so I will check them out. Thanks!
It’s also advised to use DIVIDE which handles divisions by zero gracefully.
Thanks Olivier! I will update the post…
I prefer using CALCULATE when using filters, eg =CALCULATE(DISTINCTCOUNT([UPN]), Filter 1, Filter 2). But the syntax of this is a bit less ‘Excely’ so I understand why people might use count rows approach instead – but it is tidier and easier to read in my opinion!
Nice introductory article, thank you David ?
You can also make use of the grouping facility for calculations like 4+, I have used it for phases of school and ethnicity groups as well.
Thanks Ben – I will try this!
Hi David,
I hope you’re doing well. You may find this DAX useful for calculating Term of Birth based on a pupil’s DOB:
Term of Birth = IF(MONTH(Pupil_Data[DOB])<=4,"Spring",IF(MONTH(Pupil_Data[DOB])<=8,"Summer","Autumn"))
Thanks Ahmed!
Hi David,
Great to see you at your Workshop yesterday.
Here’s the DAX to calculate the Years and Months based on DOB.
Years_Months = INT(DATEDIFF(DATEVALUE(PowerBI_Data_Primary[DOB]),TODAY(),MONTH)/12) & ” Years, ” & MOD(DATEDIFF(DATEVALUE(PowerBI_Data_Primary[DOB]),TODAY(),MONTH),12) & ” Months”
Please can you help me Iām trying to create a countif function in power bi. For admission numbers and counting a second column which have 1ās for each behaviour point they have.
Many thanks