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.07 Understand your data by using PQE to inspect column quality, column profile and column distribu
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock