For a tutorial video that walks you through the process of using the Open Data Bits library of documentation to build your own open data mashups, we have created a video that can be watched by clicking here.

Data Source Link

http://data.gov.uk/dataset/road-accidents-safety-data

Available Access Formats

CSV

Sources and Modeling

Row Color Derived Column
Row Color Advanced Implementation Column (ETL Tools Recommended)
Row Color Column Not Used by Open Data Bits

Logical Model

Calculated Columns & Calculations

Table: Vehicles0512

Name DAX Expression Type
Police_Force =RELATED(Accidents0512[Police_Force]) Calculated Column
Accident_Severity =RELATED(Accidents0512[Accident_Severity]) Calculated Column
Date =RELATED(Accidents0512[Date]) Calculated Column
Time =RELATED(Accidents0512[Time]) Calculated Column
Local_Authority_(District) =RELATED(Accidents0512[Local_Authority_(District)]) Calculated Column
Local_Authority_(Highway) =RELATED(Accidents0512[Local_Authority_(Highway)]) Calculated Column
1st_Road_Class =RELATED(Accidents0512[1st_Road_Class]) Calculated Column
Road_Type =RELATED(Accidents0512[Road_Type]) Calculated Column
Junction_Detail =RELATED(Accidents0512[Junction_Detail]) Calculated Column
Junction_Control =RELATED(Accidents0512[Junction_Control]) Calculated Column
2nd_Road_Class =RELATED(Accidents0512[2nd_Road_Class]) Calculated Column
Pedestrian_Crossing-Human_Control =RELATED(Accidents0512[Pedestrian_Crossing-Human_Control]) Calculated Column
Pedestrian_Crossing-Physical_Facilities =RELATED(Accidents0512[Pedestrian_Crossing-Physical_Facilities]) Calculated Column
Light_Conditions =RELATED(Accidents0512[Light_Conditions]) Calculated Column
Weather_Conditions =RELATED(Accidents0512[Weather_Conditions]) Calculated Column
Road_Surface_Conditions =RELATED(Accidents0512[Road_Surface_Conditions]) Calculated Column
Special_Conditions_at_Site =RELATED(Accidents0512[Special_Conditions_at_Site]) Calculated Column
Carriageway_Hazards =RELATED(Accidents0512[Carriageway_Hazards]) Calculated Column
Urban_or_Rural_Area =RELATED(Accidents0512[Urban_or_Rural_Area]) Calculated Column
Did_Police_Officer_Attend_Scene_of_Accident =RELATED(Accidents0512[Did_Police_Officer_Attend_Scene_of_Accident]) Calculated Column
LSOA_of_Accident_Location =RELATED(Accidents0512[LSOA_of_Accident_Location]) Calculated Column
Vehicles Vehicles:=COUNTROWS(‘Vehicles0512’) Calculation
Accidents Accidents:=DISTINCTCOUNT([Acc_Index]) Calculation
Vehicles per Accident Vehicles per Accident:=DIVIDE([Vehicles],[Accidents]) Calculation
Vehicles Key =CONCATENATE([Acc_Index],CONCATENATE(“-“,[Vehicle_Reference])) Calculated Column

Table: Casualty0512

Name DAX Expression Type
Police_Force =RELATED(Accidents0512[Police_Force]) Calculated Column
Accident_Severity =RELATED(Accidents0512[Accident_Severity]) Calculated Column
Date =RELATED(Accidents0512[Date]) Calculated Column
Time =RELATED(Accidents0512[Time]) Calculated Column
Local_Authority_(District) =RELATED(Accidents0512[Local_Authority_(District)]) Calculated Column
Local_Authority_(Highway) =RELATED(Accidents0512[Local_Authority_(Highway)]) Calculated Column
1st_Road_Class =RELATED(Accidents0512[1st_Road_Class]) Calculated Column
Road_Type =RELATED(Accidents0512[Road_Type]) Calculated Column
Junction_Detail =RELATED(Accidents0512[Junction_Detail]) Calculated Column
Junction_Control =RELATED(Accidents0512[Junction_Control]) Calculated Column
2nd_Road_Class =RELATED(Accidents0512[2nd_Road_Class]) Calculated Column
Pedestrian_Crossing-Human_Control =RELATED(Accidents0512[Pedestrian_Crossing-Human_Control]) Calculated Column
Pedestrian_Crossing-Physical_Facilities =RELATED(Accidents0512[Pedestrian_Crossing-Physical_Facilities]) Calculated Column
Light_Conditions =RELATED(Accidents0512[Light_Conditions]) Calculated Column
Weather_Conditions =RELATED(Accidents0512[Weather_Conditions]) Calculated Column
Road_Surface_Conditions =RELATED(Accidents0512[Road_Surface_Conditions]) Calculated Column
Special_Conditions_at_Site =RELATED(Accidents0512[Special_Conditions_at_Site]) Calculated Column
Carriageway_Hazards =RELATED(Accidents0512[Carriageway_Hazards]) Calculated Column
Urban_or_Rural_Area =RELATED(Accidents0512[Urban_or_Rural_Area]) Calculated Column
Did_Police_Officer_Attend_Scene_of_Accident =RELATED(Accidents0512[Did_Police_Officer_Attend_Scene_of_Accident]) Calculated Column
LSOA_of_Accident_Location =RELATED(Accidents0512[LSOA_of_Accident_Location]) Calculated Column
Engine_Capacity_(CC) =RELATED(Vehicles0512[Engine_Capacity_(CC)]) Calculated Column
Age_of_Vehicle =RELATED(Vehicles0512[Age_of_Vehicle]) Calculated Column
Vehicles Key =RELATED(Vehicles0512[Vehicles Key]) Calculated Column
Vehicle_Type =RELATED(Vehicles0512[Vehicle_Type]) Calculated Column
Towing_and_Articulation =RELATED(Vehicles0512[Towing_and_Articulation]) Calculated Column
Vehicle_Manoeuvre =RELATED(Vehicles0512[Vehicle_Manoeuvre]) Calculated Column
Vehicle_Location-Restricted_Lane =RELATED(Vehicles0512[Vehicle_Location-Restricted_Lane]) Calculated Column
Junction_Location =RELATED(Vehicles0512[Junction_Location]) Calculated Column
Skidding_and_Overturning =RELATED(Vehicles0512[Skidding_and_Overturning]) Calculated Column
Hit_Object_in_Carriageway =RELATED(Vehicles0512[Hit_Object_in_Carriageway]) Calculated Column
Vehicle_Leaving_Carriageway =RELATED(Accidents0512[LSOA_of_Accident_Location]) Calculated Column
Hit_Object_off_Carriageway =RELATED(Vehicles0512[Hit_Object_off_Carriageway]) Calculated Column
1st_Point_of_Impact =RELATED(Vehicles0512[1st_Point_of_Impact]) Calculated Column
Was_Vehicle_Left_Hand_Drive? =RELATED(Vehicles0512[Was_Vehicle_Left_Hand_Drive?]) Calculated Column
Journey_Purpose_of_Driver =RELATED(Vehicles0512[Journey_Purpose_of_Driver]) Calculated Column
Sex_of_Driver =RELATED(Vehicles0512[Sex_of_Driver]) Calculated Column
Age_Band_of_Driver =RELATED(Vehicles0512[Age_Band_of_Driver]) Calculated Column
Propulsion_Code =RELATED(Vehicles0512[Propulsion_Code]) Calculated Column
Driver_IMD_Decile =RELATED(Vehicles0512[Driver_IMD_Decile]) Calculated Column
Driver_Home_Area_Type =RELATED(Vehicles0512[Driver_Home_Area_Type]) Calculated Column
Casualties Casualties:=COUNTROWS(Casualty0512) Calculation
Casualties Casualties:=COUNTROWS(Casualty0512) Calculation
Casualties per Accident Casualties per Accident:=DIVIDE([Casualties],[Accidents]) Calculation

Examples for Reference

Open Data Bits – Build Your Own Great Britain Road Safety Data Model