Exercise: Power BI Dashboard

YellowLine NYC · five pages · all 12 Gold KPIs · self-paced after Module 4

Estimated time: 45–60 min (connect + model: 15 min · five pages: 30–45 min)

YellowLine NYC context (after Module 4)

Priya needs a five-page dashboard on the 12 Gold KPI tables Bob built in Databricks, Snowflake, and dbt. This exercise is self-paced — complete it after Exercise: dbt when all kpi_* tables exist.

NoteFree Power BI Desktop only

This lab uses Power BI Desktop — a free Windows app. You do not need Power BI Pro or Premium.

Included (free) Not required for this lab
Connect, model, build reports locally Sharing reports with colleagues
Save a .pbix file on your PC Premium capacity
Optional publish to My Workspace (personal) On-premises data gateway
Azure Maps geocoding in map visuals Custom shape files or ArcGIS layers

Platform: Power BI Desktop runs on Windows only (system requirements). macOS/Linux users can follow along by reading the steps or using a Windows VM.

Prerequisites

See also Power BI setup guide (install Desktop, connector steps).

Complete these exercises first so all Gold KPI tables exist:

Verify in Snowflake (if you used the dbt/Snowflake path):

SET attendee_id = 'XX_yourname';
SHOW TABLES IN SCHEMA IDENTIFIER($attendee_id || '_GOLD');
-- Expect 12 tables named KPI_* (or kpi_* depending on casing)

All 12 KPI tables — used on which page?

KPI table Dashboard page
kpi_trips_by_hour Overview · Time Analysis
kpi_trips_by_day Overview
kpi_time_of_day_analysis Overview · Time Analysis
kpi_data_quality_metrics Overview (Quality Score card)
kpi_borough_analysis Map (filled map, revenue bar)
kpi_top_pickup_zones Map (bubble map)
kpi_popular_routes Map (routes table)
kpi_revenue_by_hour Time Analysis · Revenue & Payments
kpi_payment_type_analysis Revenue & Payments
kpi_trip_efficiency Trip Efficiency
kpi_distance_bands Trip Efficiency
kpi_passenger_count_analysis Trip Efficiency

Extra DAX measures and theme JSON: Power BI dashboard guide (companion reference).

Step 0 — Install Power BI Desktop

  1. Download from Get Power BI Desktop (Microsoft Store or direct installer).
  2. Launch the app — you should see the Home ribbon, Report canvas, Data and Visualizations panes (getting started).

Step 1 — Connect to all 12 Gold KPI tables

Choose one warehouse — Snowflake is simplest if you completed the dbt exercise.

Option B: Azure Databricks

Official flow: Azure Databricks connector · Databricks partner guide

  1. Home → Get data → search Azure Databricks
  2. Enter Server hostname and HTTP path from your SQL warehouse (Databricks → SQL Warehouses → Connection details)
  3. Authentication → Personal Access Token → paste your PAT
  4. Navigator → mhpdeworkshop_databricks_2026 → {attendee_id}_gold → select all 12 kpi_* tables → Load

Checkpoint: The Data pane lists 12 kpi_* tables. No relationships are required — each KPI table is self-contained.

Step 2 — Create DAX measures

Official guide: Create measures in Power BI Desktop

  1. Modeling → New Table:
_Measures = DATATABLE("helper", STRING, {{"_"}})
  1. Right-click _Measures → Hide in report view
  2. Home → New measure — add these (one at a time):
Total Trips = SUM(kpi_trips_by_hour[total_trips])
Total Revenue = SUM(kpi_trips_by_hour[total_revenue])
Avg Fare = AVERAGE(kpi_trips_by_hour[avg_fare])
Quality Score = MAX(kpi_data_quality_metrics[data_quality_score])
Credit Card Avg Tip =
    CALCULATE(
        AVERAGE(kpi_payment_type_analysis[avg_tip_amount]),
        kpi_payment_type_analysis[payment_type] = "Credit Card"
    )

More optional measures: DAX reference.

Step 3 — Apply theme (optional)

  1. Copy theme JSON from full guide § Apply theme into a file named mhp-theme.json
  2. View → Themes → Browse for themes → select the file

Step 4 — Page 1: Overview

Add a new report page and title it Overview.

Visual Fields
4 × Card Total Trips, Total Revenue, Avg Fare, Quality Score
Line chart kpi_trips_by_hour: X pickup_hour · Y total_trips
Clustered bar kpi_trips_by_day: Y day_of_week · X total_trips — sort descending
Donut kpi_time_of_day_analysis: Legend time_of_day · Values total_trips

Checkpoint: Four cards and three charts show non-empty values.

KPI tables used on this page: kpi_trips_by_hour, kpi_trips_by_day, kpi_time_of_day_analysis, kpi_data_quality_metrics.

Step 5 — Page 2: Map — Borough & Zone Analysis

Add a page titled Map.

ImportantMaps in this workshop — Azure Maps geocoding (not lat/long)

Gold KPI tables have no latitude/longitude columns. Page 2 uses Power BI map visuals with built-in Azure Maps geocoding:

Visual How location works
Filled map Text pickup_borough (Manhattan, Brooklyn, …) → geocoded to borough polygons
Map (bubble) Text pickup_zone → geocoded to point on the map
Routes table Table visual — not a line/flow map

This is online geocoding at render time (included in Desktop). You do not need custom shape files, ArcGIS layers, Mapbox, or a “virtual layer” add-in. If zone names fail to resolve, use the Zone_Location calculated column below.

5a — Filled map (boroughs) — kpi_borough_analysis

Role Field
Location pickup_borough
Color saturation total_revenue
Tooltips total_trips, avg_fare, avg_distance

5b — Bubble map (top zones) — kpi_top_pickup_zones

Role Field
Location pickup_zone
Size total_trips
Color total_revenue
Tooltips pickup_borough, avg_fare, trip_rank
Tip

If zones do not geocode, add a calculated column on kpi_top_pickup_zones:

Zone_Location = kpi_top_pickup_zones[pickup_zone] & ", New York, NY"

Use Zone_Location in the Location field instead of pickup_zone.

5c — Popular routes table — kpi_popular_routes

  1. Insert → Table
  2. Columns: pickup_zone, dropoff_zone, total_trips, avg_fare, avg_distance
  3. Visual-level filter: top 15 by total_trips
  4. Conditional formatting: Data bars on total_trips

5d — Borough revenue bar — kpi_borough_analysis

  • Clustered bar chart: Y pickup_borough · X total_revenue

Checkpoint: Filled map shows NYC boroughs; bubble map shows zone points (table is already top 20 zones).

KPI tables used on this page: kpi_borough_analysis, kpi_top_pickup_zones, kpi_popular_routes.

Step 6 — Page 3: Time Analysis

Add a page titled Time Analysis.

Visual Fields
Matrix kpi_trips_by_hour: Rows day_type · Columns pickup_hour · Values total_trips — Format → Cell elements → Background color → Gradient (#E6F0FF → #003366)
Donut kpi_time_of_day_analysis: time_of_day · total_trips
Stacked column kpi_revenue_by_hour: X pickup_hour · Y total_revenue

KPI tables used on this page: kpi_trips_by_hour, kpi_time_of_day_analysis, kpi_revenue_by_hour.

Step 7 — Page 4: Revenue & Payments

Add a page titled Revenue.

Visual Fields
Pie chart kpi_payment_type_analysis: payment_type · total_trips
Table payment_type, total_trips, total_revenue, avg_fare, avg_tip_amount, pct_of_trips
Clustered column kpi_revenue_by_hour: X pickup_hour · Y total_revenue
Card Credit Card Avg Tip measure

KPI tables used on this page: kpi_payment_type_analysis, kpi_revenue_by_hour.

Step 8 — Page 5: Trip Efficiency

Add a page titled Efficiency.

Visual Fields
Scatter kpi_trip_efficiency: X avg_distance · Y avg_speed_mph · Size total_trips
Funnel kpi_distance_bands: distance_band · total_trips
Stacked bar kpi_passenger_count_analysis: Y passenger_count · X total_trips · color avg_fare

KPI tables used on this page: kpi_trip_efficiency, kpi_distance_bands, kpi_passenger_count_analysis.

Step 9 — Save and publish (optional)

  1. File → Save As → YellowLine-NYC-KPIs.pbix

Saving locally is enough for the self-paced lab. To publish (upload from Desktop):

  1. Home → Publish
  2. Sign in with a Microsoft account
  3. Choose My workspace (free personal workspace)

Free My workspace is personal — you cannot share the report with other users without a Pro license. Trainers demo via screen share or a pre-built .pbix.

Troubleshooting

Issue What to try
Snowflake login fails Warehouse running? Role DE_WORKSHOP_ROLE and database DE_MASTERCLASS exist?
Connector error after Desktop update See Snowflake connector notes — try latest Desktop build
Databricks connection fails SQL warehouse running; PAT not expired; use Azure Databricks connector (not generic Databricks)
Borough map blank Confirm pickup_borough values are standard names (Manhattan, Brooklyn, Queens, Bronx, Staten Island)
Zone bubble map empty / wrong city Add Zone_Location calculated column with ", New York, NY" suffix (Step 5b)
Map needs internet Azure Maps geocoding requires network access on first render
Blank visuals Data pane → confirm table loaded; check field names match Gold schema (pickup_hour not hour_of_day)

What you learned

  • Consumption layer — Power BI reads all 12 Gold KPI tables; it does not replace Databricks, Snowflake, or dbt
  • Same schema — connect to Databricks or Snowflake Gold; Priya’s five-page layout stays identical
  • Import mode — simplest path for workshop KPIs on free Desktop
  • Maps without coordinates — Azure Maps geocodes text borough/zone names; routes are a table, not a flow map

Next steps

  • Module 7 — compare your dashboard with the trainer demo; join the open tool discussion for YellowLine NYC
  • Optional modules — Module 8 adds DirectQuery streaming page; Module 9 adds predicted-tip visuals
  • DAX deep dive — Power BI dashboard guide

Official documentation