Power BI Dashboard: City Data Storytelling

Power BI Dashboard: City Data Storytelling

Why Dashboards Matter for City Stakeholders

A city department manager rarely has time to read a lengthy analysis report. What they need is a clear, interactive tool that lets them answer their own questions quickly. Power BI is one of the most widely used business intelligence platforms in government and industry, and building a well-designed dashboard is a core skill tested on the Microsoft PL-300 exam. In this lab you will connect your cleaned city dataset to Power BI Desktop, build a proper data model, write DAX measures, and publish a report that a non-technical stakeholder can navigate without your help.

Setting Up Your Data Model

A strong Power BI report begins with a clean data model, not just raw tables dropped into a file. The standard approach for analytics is the star schema: one central fact table (containing numeric measurements and foreign keys) surrounded by dimension tables (containing descriptive attributes such as neighborhood name, year, or department).

When you import your cleaned CSV into Power BI Desktop, check the following before building any visuals:

• Data types: Confirm that numeric columns are recognized as whole numbers or decimals, dates as date/datetime, and text fields as text. Wrong data types cause incorrect calculations and broken visuals.
• Relationships: Use the Model view to draw relationships between your fact table and dimension tables. A typical relationship is one-to-many (one row in the dimension matches many rows in the fact table). Set the cross-filter direction to Single unless you have a specific reason for bidirectional filtering, because bidirectional filters can produce ambiguous results.
• No circular relationships: Power BI will warn you, but always verify. A circular dependency breaks the model logic.

If your project dataset came from PostgreSQL rather than a CSV, use the PostgreSQL connector in Power BI Desktop. The modeling steps are identical once the data is loaded.

Writing DAX Measures

DAX (Data Analysis Expressions) is the formula language of Power BI. Two measure types you must include in this project are a year-over-year change and a per-capita rate.

A DAX measure lives in the report's data model, not in a column. Create measures by selecting a table in the Fields pane and choosing "New measure." Keep measure names descriptive so a stakeholder reading a tooltip understands it.

General patterns:

• Year-over-year change: Subtract the prior-year value from the current-year value. Power BI's time intelligence functions (such as `CALCULATE` combined with `SAMEPERIODLASTYEAR`) handle this when your date table is properly marked as a date table.
• Per-capita rate: Divide a count or total by a population figure. Store population in a dimension table and reference it in the measure formula.

Always test a new measure by placing it on a card visual with a known single value you can verify manually. This prevents silent errors from reaching your stakeholder.

Designing for a Non-Technical Audience

Before opening Power BI, sketch a storyboard: a simple wireframe showing each page of your report, the audience, the one key question that page answers, and the visual type chosen. Storyboarding forces you to think about communication before you think about software.

Report design principles that matter for PL-300 and for real city work:

• One question per page: Each report page should have a clear title that states what the user will learn. City stakeholders scan quickly; a cluttered page with five different topics loses them.
• Slicers for self-service: A slicer tied to year, neighborhood, or category lets the manager filter data without needing your help. Place slicers in a consistent location across pages.
• Map visuals for spatial data: City data almost always has a geographic dimension. A filled map or bubble map lets stakeholders see patterns instantly. Confirm that your location field (city, ZIP code, or latitude/longitude pair) is recognized correctly by Power BI to avoid misplotted points.
• KPI cards for headline numbers: A KPI card showing total incidents, average response time, or population served gives the stakeholder a single number to anchor the conversation.
• Accessible color palettes: Avoid red-green combinations as the sole differentiator; a significant portion of the population has difficulty distinguishing those colors. Use labels and tooltips as a second layer of information.

Publishing and the Usability Walk-Through

When your report is complete, publish it to Power BI Service using the free workspace associated with your Microsoft account. This generates a shareable link. Submit that link to your instructor.

The usability walk-through is the most important quality check in this lab. Your instructor will navigate the report as a city department manager without any help from you. If they cannot find a filter, misread a chart title, or cannot answer the page's stated question within a few minutes, that is a design failure — not a user failure. Common issues to prevent:

• Unlabeled axes or visuals with no title
• Slicers that affect only one visual when they should affect the whole page
• Measures that display too many decimal places for a general audience
• Navigation buttons between pages that are missing or hard to see

Safety and Professional Responsibility

City datasets often contain information about real residents. Before publishing any report — even to a free workspace — confirm that your dataset has been de-identified according to your project guidelines. Do not publish names, addresses, or other personally identifiable information to any cloud service without explicit authorization. Data privacy is a professional and ethical obligation, and mishandling public data can cause real harm to individuals and to your program's community relationships.

Connecting This Lab to PL-300

The PL-300 exam tests data modeling, DAX, report design, and Power BI Service tasks — exactly what this lab covers. Reviewing Microsoft Learn's PL-300 study guide after completing this lab will reinforce the terminology and concepts the exam expects. Every skill you practice here maps directly to exam objectives.