Power BI Dashboard — Full Build Guide

Five pages on the 12 Gold KPI tables · free Desktop only

NoteHow this page fits the workshop
Page Use when
Power BI setup Install Desktop and connect to Gold tables
Exercise: Power BI ~45–60 min self-paced — all five pages, all 12 KPIs
This guide Companion reference — extra DAX, theme JSON, troubleshooting

Uses Power BI Desktop (free, Windows). No Pro or Premium license required.

Prerequisites

Requirement Notes
Power BI Desktop Free — download
Gold KPI tables From Modules 2–4 (Databricks / Snowflake + dbt)
Credentials Snowflake username/password or Databricks PAT (same as pipeline exercises)

Step 1 — Connect to Gold KPI tables

Official steps: Snowflake connector

  1. Home → Get data → More… → Database → Snowflake → Connect
  2. Server: your account host · Warehouse: DE_WORKSHOP_WH
  3. Sign in with Snowflake username/password
  4. Navigator: DE_MASTERCLASS → {ATTENDEE_ID}_GOLD → select all kpi_* tables → Load
  5. Choose Import when prompted

Official steps: Azure Databricks connector

  1. Home → Get data → search Azure Databricks
  2. Server hostname + HTTP path from SQL warehouse connection details
  3. Personal Access Token
  4. Navigator: mhpdeworkshop_databricks_2026 → {attendee_id}_gold → all kpi_* → Load

After loading, the Data pane should list 12 tables:

kpi_borough_analysis kpi_popular_routes
kpi_data_quality_metrics kpi_revenue_by_hour
kpi_distance_bands kpi_time_of_day_analysis
kpi_passenger_count_analysis kpi_top_pickup_zones
kpi_payment_type_analysis kpi_trip_efficiency
kpi_trips_by_day kpi_trips_by_hour

No relationships are needed — each KPI table is self-contained.

KPI coverage (all 12 used)

KPI table Dashboard page
kpi_trips_by_hour Overview (line chart) · Time Analysis (matrix)
kpi_trips_by_day Overview (bar chart)
kpi_time_of_day_analysis Overview · Time Analysis (donut)
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 (pie, table, tip card)
kpi_trip_efficiency Trip Efficiency (scatter)
kpi_distance_bands Trip Efficiency (funnel)
kpi_passenger_count_analysis Trip Efficiency (stacked bar)

Hands-on path: follow Exercise: Power BI for step-by-step page builds. Use this guide for extra DAX and theme JSON.

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 at minimum:
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])

Additional copy-paste measures are in DAX measures below.

Step 3 — Apply theme (optional)

  1. Copy the JSON below into a new file named mhp-theme.json on your PC (e.g. Desktop)
  2. In Power BI Desktop: View → Themes → Browse for themes → select mhp-theme.json
Note

All theme and DAX content lives on this page — you do not need repo files such as powerbi/mhp-theme.json or powerbi/dax_measures.md.

Show MHP theme JSON — copy into a file named mhp-theme.json 
{
    "name": "MHP Data Engineer Workshop",
    "dataColors": [
        "#003366",
        "#0066CC",
        "#3399FF",
        "#66CCFF",
        "#FF6600",
        "#FF9933",
        "#FFCC00",
        "#99CC33"
    ],
    "background": "#FFFFFF",
    "foreground": "#333333",
    "tableAccent": "#003366",
    "good": "#99CC33",
    "neutral": "#FFCC00",
    "bad": "#FF6600",
    "maximum": "#003366",
    "center": "#66CCFF",
    "minimum": "#E6F0FF",
    "textClasses": {
        "callout": {
            "fontSize": 45,
            "fontFace": "Segoe UI Light",
            "color": "#003366"
        },
        "title": {
            "fontSize": 12,
            "fontFace": "Segoe UI Semibold",
            "color": "#333333"
        },
        "header": {
            "fontSize": 12,
            "fontFace": "Segoe UI",
            "color": "#333333"
        },
        "label": {
            "fontSize": 10,
            "fontFace": "Segoe UI",
            "color": "#666666"
        }
    },
    "visualStyles": {
        "*": {
            "*": {
                "background": [
                    {
                        "color": {
                            "solid": {
                                "color": "#FFFFFF"
                            }
                        }
                    }
                ],
                "border": [
                    {
                        "color": {
                            "solid": {
                                "color": "#E0E0E0"
                            }
                        }
                    }
                ],
                "outlineColor": [
                    {
                        "solid": {
                            "color": "#E0E0E0"
                        }
                    }
                ]
            }
        },
        "page": {
            "*": {
                "background": [
                    {
                        "color": {
                            "solid": {
                                "color": "#F5F5F5"
                            }
                        }
                    },
                    {
                        "transparency": 0
                    }
                ]
            }
        }
    }
}

Step 4 — Build dashboard pages

Page 1: Overview

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

Page 2: Map — Borough & Zone Analysis

Note

Maps use Azure Maps geocoding — Gold tables have text pickup_borough / pickup_zone, not lat/long. Power BI resolves locations online at render time (free in Desktop). No custom shape files or ArcGIS layers. kpi_popular_routes is a table, not a route line map.

2a — Filled map (boroughs)

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

Power BI geocodes Manhattan, Brooklyn, Queens, Bronx, Staten Island automatically.

2b — Bubble map (top zones)

Role Field
Location kpi_top_pickup_zones[pickup_zone]
Size total_trips
Color total_revenue
Filter (optional) trip_rank ≤ 20 — table already returns top 20
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.

2c — Popular routes table (kpi_popular_routes) — columns: pickup_zone, dropoff_zone, total_trips, avg_fare, avg_distance; top 15 by trips; data bars on total_trips.

2d — Borough revenue bar — Y: pickup_borough, X: total_revenue from kpi_borough_analysis.

Page 3: Time Analysis

Visual Fields
Matrix (heatmap) From kpi_trips_by_hour: Rows day_type · Columns pickup_hour · Values total_trips — gradient background #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

Page 4: Revenue & Payments

Visual Fields
Pie chart kpi_payment_type_analysis: payment_type · total_trips
Table kpi_payment_type_analysis: 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
KPI card Credit Card Avg Tip measure (from kpi_payment_type_analysis)

Page 5: Trip 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

Step 5 — Publish (optional, free tier)

Publish from Power BI Desktop:

  1. Home → Publish
  2. Sign in with a Microsoft account
  3. Destination: My Workspace

My Workspace is personal — you cannot share with other users on the free tier. Trainers screen-share from Desktop or their own published report.

DAX measures reference

Formatting

Revenue Formatted = FORMAT([Total Revenue], "$#,##0")
Quality Badge =
    IF(
        [Quality Score] >= 95, "Good",
        IF([Quality Score] >= 80, "Fair", "Poor")
    )

Borough & zone

Top Borough =
    FIRSTNONBLANK(
        TOPN(1, VALUES(kpi_borough_analysis[pickup_borough]), kpi_borough_analysis[total_trips], DESC),
        1
    )
Borough Revenue Share =
    DIVIDE(
        SUM(kpi_borough_analysis[total_revenue]),
        CALCULATE(SUM(kpi_borough_analysis[total_revenue]), ALL(kpi_borough_analysis))
    )

Time analysis

Peak Hour =
    FIRSTNONBLANK(
        TOPN(1, VALUES(kpi_trips_by_hour[pickup_hour]), kpi_trips_by_hour[total_trips], DESC),
        1
    )
Busiest Day =
    FIRSTNONBLANK(
        TOPN(1, VALUES(kpi_trips_by_day[day_of_week]), kpi_trips_by_day[total_trips], DESC),
        1
    )

Revenue

Credit Card Avg Tip =
    CALCULATE(
        AVERAGE(kpi_payment_type_analysis[avg_tip_amount]),
        kpi_payment_type_analysis[payment_type] = "Credit Card"
    )
Cash Trip Pct =
    CALCULATE(
        MAX(kpi_payment_type_analysis[pct_of_trips]),
        kpi_payment_type_analysis[payment_type] = "Cash"
    )

Calculated columns

Hour_Label = FORMAT(kpi_trips_by_hour[pickup_hour], "00") & ":00"

Troubleshooting

Issue Solution
Map shows wrong locations Use Zone_Location calculated column (see Page 2)
Zones outside NYC Table is pre-filtered to top 20; use Zone_Location column if geocoding fails
Databricks connection fails SQL warehouse running; correct HTTP path
Snowflake timeout Resume DE_WORKSHOP_WH
Borough map blank Standard borough names (Manhattan, Brooklyn, …)
Link from module page 404 Use this page — not the GitHub powerbi/README.md path

Official documentation