Power BI for Crime Analysts

Power BI connects to a wide range of data sources, including flat files like CSV and CAD data. Refresh data in Model View to keep dashboards up to date.

Save frequently in Power BI by using file save or save as, note there isn’t an auto save feature, and enable auto recovery with five or thirty minute intervals.

Add text and images to your Power BI dashboard, format text boxes, apply color schemes, and place a badge image or watermark to create data-driven visualizations.

Power BI for crime analysts presents a fixed offense table that gains new rows; as of 2024-12-15, 10,000 rows exist (earlier versions show 8,000), and date slicers use relative dates.

Build data-driven visualizations in Power BI by creating cards to count offenses, distinguish offense vs report numbers, apply aggregation, and filter for part one offenses and the previous 28 days.

Learn how to safely add visuals in power bi by deselecting cards with a click on the background, preventing cards from being replaced when inserting slicers.

Learn to add and format slicers in Power BI for crime analysts, including date range and officer field slicers, with format painter and drop-down display options.

Create and customize pie and ring charts in Power BI dashboards to compare yes/no victim injury counts, using legend fields and formatting options.

Build a top-ten offenses bar chart in Power BI for crime analysis by counting offenses by nibrs code, using a stacked bar chart and top N filter.

Build and compare table and matrix views on a crime analytics dashboard, displaying narratives, dates, officers, and reports, then aggregate offenses by zones with conditional formatting.

Build and analyze line charts in Power BI by using date hierarchies on the x-axis, counting offenses, and exploring drilldown, expand, anomalies, and forecast features.

Enable ArcGIS for Power BI and build a Bing map using latitude and longitude to plot incidents, then use lasso, adjust base maps, bubbles, and tooltips.

Learn how to build reliable maps in Power BI by concatenating address, city, and state with DAX to improve geocoding, manage lat/long data, and visualize accurate locations.

Improve address mapping in Power BI for small jurisdictions by hard-coding a zip in the address field to tighten clustering around major cities like Las Vegas.

Explore Esri maps in Power BI, with login options and layers from the Esri catalog. Visualize crime data using points, heatmaps, and clustering, and switch between Esri and Bing maps.

Discover how to add visuals beyond the built-in options in Power BI through the marketplace. Use the Chicklet slicer to customize tiles, colors on selection, and text, expanding slicer control.

Use DAX to create a year column from date occurred and plot a multi-year line chart that overlays yearly trends, comparing offenses by month.

Learn to group offenses into broader categories with DAX and create a new Power BI column, mapping to NIBRS and UCR groups using ChatGPT.

Learn to group NIBRS codes in Power BI using the built-in Groups feature, creating a new grouped column to classify offenses like homicide, burglary, and larceny without writing DAX.

Roche's maxim of data transformation urges transforming data upstream toward the source database and downstream toward the data warehouse and Power BI.

Explore custom tooltips that create a dashboard within a dashboard in Power BI, showing how hover details reveal aggregated counts, breakdowns by NIBRS codes, and narrative data for field analysis.

Create a tooltip page in Power BI to show offence codes when hovering the bar chart, then tighten the tooltip canvas for a concise larceny breakdown.

Build a dedicated tooltip page in Power BI comprising a table with officer, report number, and narrative, then assign it to the crime map tooltip.

Understand how relational databases use tables, rows, and columns to model one-to-one and one-to-many relationships between reports, offenses, suspects, and victims, enabling sql queries and edge lists for link charts.

Model data in Power BI by linking offenses and CAD data with a central date table, using slicers to filter across datasets and avoid many-to-many connections.

Build a Power BI model to filter Ms. offenses and CAD by zone, create a zone lookup, and configure one-to-many relationships with slicers.

Create a date table in Power BI and add date-only columns for CAD and MBS, then link them via one-to-many relationships to enable a date slicer that filters both visuals.

Filter CAD and RMS data by officer names in Power BI, create a distinct officer roster with DAX, and build a slicer to analyze reports and calls by officer.

Finish building the Ms. and cad dashboard by adding visuals, copying cards, and creating bar and line graphs with coherent axes, and model slicers for Officer Bailey's calls for service.

Power BI for crime analysts teaches building a day-of-week and hour-of-day heat map, starting with an incorrect version to illustrate data prep, matrix setup, and conditional formatting for crime counts.

Build a day-of-week and hour-of-day heat map in power bi by ordering days with a helper column, loading data from excel, and using a matrix with conditional formatting.

Explore how Power BI measures enable dynamic calculations that update with user input, building a CompStat-style crime analysis table with year-to-date, 28-day, and 90-day measures and percent changes.

Create a simple Power BI table, add UCR groups as the first column to build the CompStat view, then prepare to include counts and percent changes in upcoming steps.

Build a compstat table in Power BI by creating DAX measures for current year-to-date counts, previous year-to-date counts, 28-day counts, a three-year average, and percent changes with conditional formatting.

Format the CompStat table to show year-to-date and prior-year counts, 28-day and 90-day windows with percent changes, and three-year averages.

Switch from gradient to rule-based conditional formatting in CompStat, using number values to display positive changes in red, negative changes in green, and zero changes in white for quick insights.

Power BI for crime analysts: rewrite 28-day and 90-day date measures to reference the maximum slicer date. Ensure persisting counts and validate with a date range check.

Replace the today function with a max function for the 90-day and year-to-date measures, copy changes from the 28-day measure, test ranges in table view, and adjust the year-to-date logic.

Learn to implement three-year averages in Power BI by adjusting date ranges with max date logic, updating 28-day, 90-day, and year-to-date measures, and validating data constraints.

Learn to create bookmarks and dashboard buttons in Power BI to automatically apply date ranges and zone filters, simplifying slicer interactions for non-technical officers.

Power BI for crime analysts demonstrates building interactive buttons and bookmarks, including a 28-day bookmark and clear all visuals button, using a relative date slicer and drill down to days.

Navigate the Power BI app to the workspaces tab, open the demo dashboard, view mobile widgets, the CompStat table, and tooltip pages, adjust the date range, and switch pages.

Export the dashboard to pdf from the file menu, producing a multi-page document of all visible pages; hide tooltip and offender workup pages to generate a clean three-page pdf.

Explore the offender workup dashboard in Power BI for crime analysts, select a suspect, view stats on offenses involved, arrestee or suspect counts, victims, plus a contact map and narratives.

Create an offender map in Power BI using Bing maps and offenses table, map latitude and longitude (addresses optional) to count offenses, name layer, and list narrative information for offenses.

In Power BI, link the offenses table to incident numbers to display narratives for selected incidents in a resizable table for officers.

Explore modeling a connected known associates table in Power BI for crime analytics, addressing slicer filters and one-to-many relationships, by using a copied person table to drive visuals.

Create a new table named person event two with DAX to link to Ms offenses, forming a one-to-many relationship with a single cross filter, enabling name-based filtering of connected people.