Autoplay
Autocomplete
Previous Lesson
Complete and Continue
Power BI Further Skills
About this course
Welcome (4:32)
Week 1: Introduction to DAX
1.00 Before we start - deselect auto date and auto relationships (2:42)
1.01 What is DAX? (2:23)
1.02 We've already used DAX before (1:37)
1.03 Setting up a new report file - without the auto relationships (5:09)
1.04 Create a measure to count pupils (7:36)
1.05 Two measures to show the earliest and latest birthday (6:29)
1.06 Sum and average minutes late (9:38)
1.07 Understanding measures using a simple table - and introducing 'context' (10:09)
1.08 Creating a measures table help organise your measures (4:45)
1.09 Using a calculated column to calculate percentage attendance (7:18)
1.10 Using a calculated column for persistent absentees (3:43)
1.11 A tip for working with measures and calculated columns (7:45)
1.12 Why measures are essential to calculate average percentage attendance (5:34)
1.13 Calculated column or measure? A guide. (3:09)
1.14 Counting boys and girls (8:11)
1.15 Pupils in multiple categories (5:15)
Appendix 1: Resources for this section of the course
Week 1 webinar recording (48:04)
Data sources and the power query editor
2.01 Importing DFE performance data as an excel file (5:42)
2.02 Using OneDrive for Business to host your data files (6:52)
Extracting data from tables on webpages (2:55)
Extract pupil data from SIMS (11:28)
Save your SIMS report for next time (2:17)
Cleaning up pupil data in the Power Query Editor (16:56)
Merging two columns together (forename and surname) (3:23)
Extracting pupil photographs from SIMS (5:56)
Importing and using pupil photos in a pupil dashboard (10:09)
Adding tooltips to a table or chart (8:51)
Creating a year group index column (7:58)
Using Bromcom with Power BI (3:08)
Adding a column to the SIMS extract report (5:56)
Tip: adding a column to your original CSV file (5:56)
Duplicating tables and merging two tables together (14:14)
Using power query to anonymise pupil names (15:34)
Live webinar for week 2 (30:16)
Appendix 2: Resources for this section of the course
Pupil data - formatting, calculating and analysing
About this part of the course
Before we start - tidying up - deleting columns and applied steps in the Power Query Editor (11:37)
Using DAX to create a calculated column for a pupil's age in years (3:42)
Using DAX to calculate a 'years in school' column (2:03)
Revision - calculated columns for percentage attendance and persitent absentees YTD (6:18)
Revision - Count pupils using COUNTROWS and create a measures table (5:25)
Revision - more COUNTROWS measures using FILTER and IN (7:26)
Ethnicity measure and a tip for formatting your DAX code (6:58)
Revision - Calculating percentages using measures (5:58)
Calculating persistent absentees and percentage absence measures (8:12)
Page one of your new report (part 1) (12:03)
Page one of your new report (part 2) (9:48)
Using Drill Downs to analyse within groups of pupils (9:37)
Using tooltips to add context to graphs (7:54)
Creating groups to band together year groups into key stages - and more (9:38)
Using bookmarks and buttons to allow users to reset the report (8:29)
Appendix 1: Extracting attendance year to date statistics from SIMS (17:24)
Appendix 2: Webinar 12th June 2020 - and how to set up a 'drill through' (38:20)
Appendix 3: pbix file
Attendance
Introduction to attendance marks analysis (1:33)
Extracting attendance marks from SIMS part 1 (11:44)
Extracting attendace marks from SIMS part 2 (3:51)
Loading attendance marks by appending three tables together (9:29)
Using an excel file as a lookup table (9:01)
Inspecting our attendance data (7:40)
Cleaning blank, null or error values from the csv file using Power Query Editor (7:20)
Filtering out school leavers from attendance marks (2:23)
New: Filtering out attendance marks for today or in the future (6:22)
Create the percentage attendance measures (20:02)
Power BI and dates - an introduction to date tables (10:25)
Tips for attendance graphs (3:18)
Create a date table using the CALENDAR function (6:43)
Calculating the academic year and adding it to the date table (15:29)
Calculating the term from the date (6:02)
New: Calculating the term - accurately and allowing for Easter
Adding 'academic year and term' and 'academic and week number' columns to the date table (5:36)
Setting up a date hierarchy (3:35)
Calculating the percentage absence rate (5:22)
Introducing calculate (6:57)
Rolling 30-day attendance - method 1 (10:04)
Rolling 30-day attendance - method 2 (4:03)
Calculating the 30 day percentage attendance for a pupil - using a calculated column (6:49)
Understanding context using MAX(date) - and introducing the SAMEPERIODLASTYEAR function (11:45)
New: Using a measure to apply conditional formatting 'traffic lights' (9:43)
Bringing it all together...create an attendance page (13:24)
New: An alternative way to count the possible sessions for dual registered pupils (5:26)
New: Identifying persistent absentees over variable timesclaes (eg this week, last month etc) (9:12)
Appendix 1: Attendance marks csv files
Appendix 2: Attendance mark descriptions excel file
Appendix 3: Webinar June 19th at 10am (59:50)
Appendix 4: pbix file
Assessment data visualization techniques - part 1
Introduction (1:02)
Aspect naming conventions for SIMS assessment manager (3:46)
Three methods for extracting assessment data (1:46)
Method 1 - extract all the results into one big file (9:15)
Method 2 - a report for each subject with links to classes (13:30)
Method 3 - export classes and merge with assessment results (22:23)
Importing assessment data into power query editor (10:37)
Extracting information from the aspect name to create new columns we can filter (10:01)
More about relationships (it's complicated!) and a hierarchical slicer (7:53)
Calculate an average grade measure (3:41)
A measure to count grades (2:43)
Measure to calculate how many pupils have a grade 5 or higher (5:42)
Calculate the percentage of pupils at grade 5 or higher (4:16)
Remember - we aren't just looking at attainment grades (2:00)
Average grades for PP, Gender and SEN (6:58)
Extracting KS2 prior attainment from SIMS (9:16)
Pivoting the KS2 prior attainment data to create 4 new columns (7:07)
Create a KS2 low-medium-high prior attainment column (5:48)
Comparing target and actual grades to calculate a residual (12:54)
Free access code for the KS4 Resources course
Using the KS4 Resources with Power BI (4:52)
Exporting A8 and P8 data from SIMS (7:04)
Using the SWITCH command to convert grades to marks (and marks to grades) (7:27)
Appendix 1: pbix file
Appendix 2: csv source files
Appendix 3: Webinar (75:14)
Behaviour, Achievement and Exclusions
6.01 Including leavers in our pupil data - part 1 extracting from SIMS (3:33)
6.02 Dealing with leavers and on roll pupils within Power BI (7:59)
6.03 Extracting behaviour data from SIMS (8:38)
6.04 Exporting achievement from SIMS (4:11)
6.05 Exclusions data from SIMS (4:29)
6.06 Cleaning up the behaviour data using power query editor (8:57)
6.07 Understand your data by using PQE to inspect column quality, column profile and column distribu (4:41)
6.08 Measures to sum, average and count behaviour points and incidents (5:16)
6.09 Create a date table for behaviour dates (9:45)
6.10 Using PQE to create a 'behaviour category' column - especially useful for MATs (8:33)
6.11 Live webinar - reducing the size of the attendance marks file plus understanding relationships (42:26)
6.12 Rolling and static averages for behaviour (5:59)
6.13 Achievements. acheivement measures and how to use data model layouts (15:04)
6.14 Exclusions (11:50)
6.15 Decomposition trees (8:27)
6.16 Creating a venn diagram for KS2 results using an imported visualization (17:30)
Appendix 1 - csv files
Automation and using the Power BI service
7.00 Introduction - the 'big picture' for power bi (8:46)
7.01 Automating our csv files part 1 (4:44)
7.02 Automating our csv files part 2 (19:48)
7.03 Automating our csv files part 3 (12:28)
7.04 Dealing with errors when we refresh our data (3:59)
7.07 Sharing reports with colleagues using the pro license (4:16)
7.05 Viewing and presenting using the Power BI Service (7:30)
7.08 Subscribing to reports to receive email updates (4:07)
7.06 Power BI service - export to powerpoint and pdf (3:26)
7.07 Live webinar - automating and sharing (70:36)
Designing your report and bringing everything together
8.01 Tidying up - and a few tips for organising your report (8:43)
8.02 Using, finding and creating Power BI themes (6:06)
8.03 Creating your own theme to match the school logo colours (7:24)
8.04 Finding background images and using fonts (5:41)
8.05 Using powerpoint to create a simple page background with logo (1:49)
8.06 Formatting your report pages using grids (3:16)
8.07 Adding buttons to navigate to specific pages and create a table of contents (5:43)
8.08 Using icons in Power BI and adding icons to buttons (6:18)
8.09 Linking buttons to bookmarks (10:10)
8.10 Styling the whole school summary page (9:20)
8.11 Setting up the filter pane for our teachers (4:59)
8.12 Adding a 'pupil demographics ' page (6:37)
8.14 Aligning and grouping visualizations for neatness (5:17)
8.13 Create an SEN YesNO column and visualize the data in a donut chart (5:38)
8.15 A venn diagram to identify pupils with multiple vulnerabilities (17:04)
8.16 DAX revision - academic week and academic month numbers (11:12)
8.17 Live webinar - attendance trends and absence reasons (61:45)
8.17 Fine tuning and duplicating attendance pages to develop behaviour graphs (9:53)
Visualizing assessment data - part 2
9.01 Introduction to assessment data analysis (7:26)
9.02 Linking national statistics to the assessment tables using an Excel spreadsheet (11:24)
9.03 Creating a KS4 results table including residuals and comparisons to national averages (18:58)
9.04 Live webinar - creating trend and class analysis pages for individual subjects (68:58)
Feedback and certification
Read me
6.10 Using PQE to create a 'behaviour category' column - especially useful for MATs
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock