An interactive Power BI dashboard over five years (2020 to 2024) of South Australia road crash data, joining four related datasets into a single model and letting users explore patterns by location, time, severity, and impairment.
South Australia's road crash data (via DATA.SA) is public but sits in large, disconnected flat files, with no easy way to see where, when, or under what conditions serious and fatal crashes cluster. I wanted to turn five years of incident-level records into an explorable dashboard that surfaces those patterns, by location, time of day, severity, and impairment, in a way a policymaker or analyst could actually act on.
Data model. Four tables joined on the crash report ID: Crash as the fact table, Casualty and Units as detail tables (one row per injured person and per involved vehicle), and LatLon as a lookup that carries the coordinates for the map. That structure gives one point of truth per crash while preserving per-person and per-vehicle detail without duplication.
DAX measures. Total Crashes, Total Fatalities, Total Casualties, Total Serious Injuries, Impaired Crashes (crashes flagged with DUI or drug involvement), and Killed or Seriously Injured (KSI = fatal + serious). Every KPI on the report is a measure, not a static number, so filters propagate everywhere consistently.
Slicers and cross-filtering. Year range, severity level, and Local Government Area (LGA), all cross-filter every visual simultaneously.
Visuals. A scatter map of crash locations coloured by severity (drawn from the LatLon table), KPI cards for the headline numbers, a year-over-year serious injury and fatality bar chart, and a casualties-by-hour line chart that surfaces the daily peak.
