3. Power BI fundamentals for crash safety

Crash safety analysis lives or dies on the quality of its data model. Before a single visual is placed on a report canvas, the analyst must answer three questions: where does the crash data come from, how do the tables relate to one another, and how will the spatial dimension be represented? This chapter walks through the practical mechanics of importing crash records into Power BI, establishing a clean star-schema relationship layer, and assembling a dashboard layout anchored by a crash location map. The emphasis is on reproducible steps and on the decisions that most often go wrong in real projects.

Importing Crash Data with the Right Fidelity

Crash databases typically arrive in one of three shapes: a flat CSV export from a police or insurance system, an Excel workbook with one sheet per year, or a relational source such as SQL Server. Power BI Desktop's Get Data experience handles all three, but the import path matters for downstream performance and correctness.

For CSV and Excel sources, use Get Data → Text/CSV or Excel workbook, then open Transform Data to enter Power Query before loading. This is not optional polish — it is where you fix the issues that would otherwise poison the model. Typical crash exports contain:

• Mixed date formats — e.g., 2023-04-12 alongside 04/12/2023. Set the column type explicitly to Date and verify with a filter on the column.
• Latitude/longitude stored as text with comma decimal separators (52,3701). Use Replace Values to swap the comma for a period, then change the type to Decimal Number.
• Null coordinates for crashes recorded without GPS. Keep these rows but flag them; a map visual will silently drop them, and you need to know how many are missing.
• Duplicate crash IDs from merged sources. Use Remove Duplicates on the crash identifier column, but first confirm the duplicates are true duplicates rather than multiple vehicles in one crash.

A practical import pattern is to load a single FactCrash table containing one row per crash, with columns such as CrashID, CrashDate, Severity, Latitude, Longitude, RoadClass, SpeedLimit, VehicleCount, and CasualtyCount. Keep descriptive attributes (road class, severity band, region) in separate dimension tables rather than repeating them in the fact table. This is the star schema that makes DAX measures fast and predictable.

Building the Relationship Layer

Once the tables are loaded, switch to Model view. Power BI will auto-detect relationships when column names match, but auto-detection is unreliable with crash data because identifiers are often numeric and ambiguous. Create relationships manually and verify each one.

The core relationships for a crash safety model are:

Two rules prevent most relationship bugs. First, every relationship should be many-to-one from the fact table to a dimension, with the dimension's key column marked as a unique identifier. Second, cross-filter direction should stay Single unless you have a specific reason to use Both; bidirectional filters create ambiguous filter paths and slow down measures. If you find yourself needing bidirectional filtering, the model usually needs a bridge table instead.

Mark DimDate as a date table (Table tools → Mark as date table) so that time intelligence functions such as TOTALYTD and SAMEPERIODLASTYEAR behave correctly. Without this, DAX time functions fall back to the fact table's date column and produce subtly wrong results around year boundaries.

Laying Out the Dashboard and the Crash Location Map

With the model in place, the report canvas becomes a design problem. A crash safety dashboard typically serves three audiences: safety engineers reviewing severity trends, planners examining spatial clusters, and management tracking KPI movement. The layout should let each audience find their answer without scrolling.

A workable arrangement for a 1280×720 canvas:

• Top band (y ≈ 0–90 px): a row of KPI cards — total crashes, fatal crashes, serious injuries, and a year-over-year change measure. Keep to four cards; more dilutes attention.
• Left column (x ≈ 0–380 px): slicers for date range, severity, and road class, stacked vertically. Place slicers where the eye lands first, because they control everything else.
• Center (x ≈ 400–900 px): the crash location map, occupying the largest area. This is the spatial anchor of the report.
• Right column (x ≈ 920–1280 px): a severity distribution bar chart and a monthly trend line, stacked.

For the map itself, use the Azure Maps visual (or the built-in Map visual if Azure Maps is unavailable). Add FactCrash[Latitude] to the Latitude field and FactCrash[Longitude] to the Longitude field. Set the Size property to CasualtyCount and the Legend to Severity so that fatal crashes appear as larger, distinctly colored bubbles. In the formatting pane, enable Bubble layer → Clustering when the point count exceeds a few thousand; clustering prevents overplotting and reveals density hotspots that individual points would hide.

After placing the map, test the interaction chain. Click a severity slicer value and confirm the map, bar chart, and trend line all filter. Click a bubble on the map and confirm the KPI cards update. If a visual does not respond, the relationship is likely missing or the cross-filter direction is wrong. Use Edit interactions (Format → Edit interactions) to control which visuals filter which, and disable filtering from the map to the KPI cards if you want the headline numbers to remain stable while exploring locations.

Verifying the Model Before Publishing

Before publishing to the Power BI Service, run three checks. First, open Performance Analyzer and refresh each visual; any visual taking more than about two seconds on import mode signals a missing relationship or an overly complex measure. Second, cross-check a KPI card against a direct query on the source — for example, confirm that the total crash count for a selected month matches a SELECT COUNT(*) filtered to the same period. Third, verify that null-coordinate crashes are accounted for: add a card showing COUNTROWS(FILTER(FactCrash, ISBLANK(FactCrash[Latitude]))) so the map's coverage gap is visible rather than hidden.

The result of this chapter's work is a report that loads clean crash records, relates them through a star schema, and presents them on a layout where the crash location map is the spatial centerpiece. That foundation is what every subsequent DAX measure and every advanced visual will build on.