Power BI — DAX & Theme Reference

Companion to the Power BI exercise · optional measures and MHP theme JSON

NoteHow this page fits the workshop
Page Use when
Power BI setup Install Desktop and connect to Gold tables
Exercise: Power BI ~50–70 min self-paced — connect, DAX basics, all five pages, all 12 KPIs
This reference Copy-paste extra DAX, theme JSON, and troubleshooting — after you start the exercise

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

KPI coverage (all 12 used)

# Priya’s question Gold tables Dashboard page
1 Peak revenue hours kpi_revenue_by_hour, kpi_trips_by_hour Time Analysis · Revenue & Payments
2 Top pickup zones kpi_top_pickup_zones Map
3 Popular routes kpi_popular_routes Map
4 Borough + distance band kpi_borough_analysis, kpi_distance_bands Map · Trip Efficiency
5 Bad data guardrails Silver filters + kpi_data_quality_metrics Overview
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, Records Removed, Retention Rate)
kpi_borough_analysis Map (shape map, revenue bar)
kpi_top_pickup_zones Map (zone 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)

MHP theme

  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 extended 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
                    }
                ]
            }
        }
    }
}

DAX measures reference

Create a _Measures table first — see Exercise: Power BI § Step 2.
Official guide: Create measures in Power BI Desktop

Formatting

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

Data quality (Priya Q5)

Silver drops null fares, zero-distance trips, and other invalid rows before Gold runs.

Records Removed =
    CALCULATE(
        MAX(kpi_data_quality_metrics[metric_value]),
        kpi_data_quality_metrics[metric_name] = "records_removed"
    )
Retention Rate Pct =
    CALCULATE(
        MAX(kpi_data_quality_metrics[metric_value]),
        kpi_data_quality_metrics[metric_name] = "retention_rate_pct"
    )

Format Retention Rate Pct as Percentage on Overview cards.

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]),
        kpi_payment_type_analysis[payment_type_desc] = "Credit card"
    )
Cash Trip Pct =
    CALCULATE(
        MAX(kpi_payment_type_analysis[pct_of_total]),
        kpi_payment_type_analysis[payment_type_desc] = "Cash"
    )

Calculated columns

Table: kpi_top_pickup_zones

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

Table: kpi_trips_by_hour

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

Troubleshooting

Issue Solution
Shape map blank / upload fails Upload powerbi/nyc-boroughs.topojson; or GeoJSON from NYC Open Data → convert at mapshaper.org; enable Shape map in Preview features
Zone map plots outside NYC Add Zone_Location calculated column — see DAX § Calculated columns
Map disabled by organisation Use borough bar chart + zone table instead of maps
Quality Score measure — column not found Table is long-format: use metric_name / metric_value — see exercise Step 2
Snowflake Navigator empty / no kpi_* Wrong Gold schema — _DBT_GOLD (dbt), _SQL_GOLD (SQL only), or _SP_GOLD (Snowpark); not plain _GOLD
Snowflake MFA / PEM / login errors Use key-pair auth — see exercise § Snowflake
Databricks catalog error in Navigator Advanced options → Catalog = mhpdeworkshop_databricks_2026
Link from module page 404 Use the exercise on the workshop site — not the GitHub powerbi/README.md path

For connect, publish, and page-build issues, see Exercise: Power BI.

Official documentation