Case 04 · Power BI · Metrics reporting
Multi-Gate Priority Dashboard
A Power BI reporting system that merges three source systems, scores every community against two readiness gates, and ranks an entire construction pipeline into four actionable tiers. It refreshes daily and emails each manager their own report every week.
Act here first.
Resolve the other.
Monitor only.
Reference only.
The problem
"Urgent" and "buildable" weren't the same thing.
Managers needed to know which communities to work on this week. But the two facts that decide whether a community is actually buildable, whether its feeder infrastructure is confirmed and whether it is approved for funding, lived in two separate Excel trackers, disconnected from the primary list of properties.
So prioritization happened by hand and by memory. A community with a construction date two weeks out looked critical, right up until someone discovered it was blocked and could not be built at all. Time got spent chasing sites that weren't ready, while genuinely actionable ones sat in the same undifferentiated list.
- Readiness signals were split across three systems that never talked to each other.
- Near-term but blocked communities masqueraded as top priorities.
- Sorting the pipeline was a manual, repeated spreadsheet chore.
- Orphaned properties from a departed manager went unnoticed for weeks.
The solution
One model scores the whole pipeline, every morning.
A single Power BI data model joins the primary property list to both readiness trackers, evaluates two gates, resolves the most trustworthy construction date, and assigns every community a tier and an urgency. From there it refreshes itself daily and emails each manager their own slice.
Unify three systems
Power Query merges the SharePoint primary list with two Excel trackers, a dependency tracker and a finance tracker, on a shared property key.
Evaluate readiness
Two gates are scored per community: is the feeder dependency confirmed, and is finance approved? These decide whether a site can actually be built.
Pick the date that counts
Four possible construction dates across two systems collapse into one effective date, using a fixed order of authority so the most committed date always wins.
Assign a tier
Each community lands in one of four tiers by gate status, then gets sub-ranked by days until it starts, running from Overdue to Critical, High, Medium, and Low.
Report & route it
A three-page report gives leadership the whole pipeline; a Power Automate flow emails each manager their filtered PDF every Monday, so no one has to open a dashboard to know their week.
Key technique
Resolving the date that counts.
The same community can carry up to four construction dates spread across two systems. Rather than trust whichever one a person happens to look at, the model resolves a single effective date through a fixed ladder of authority, where a committed and financed date always beats an unfunded estimate.
A real, financed start date already logged. The strongest signal, so it wins whenever it exists.
A financed, committed estimate. Trusted next, because the funding is approved.
The field team's best estimate before funding, used as the fallback when finance has not cleared.
The last resort, a recorded date with no financial commitment behind it yet.
A parallel SWITCH in DAX turns the resolved tier into its color, background, and icon, so a single classification drives every visual cue on the page without a second rule to maintain.
Logic
How a community finds its tier.
The gates live entirely in the source data. When a feeder code is entered or finance flips to approved, the community re-tiers itself on the next morning's refresh, and no one touches the report.
Trust the numbers
I checked the model against the raw data.
A dashboard is only useful if people believe it. Before handing it to leadership, I cross-checked every headline figure against the source files independently, reconciling record counts, gate totals, and tier sizes system by system.
The cross-check paid for itself. It surfaced a table column that was hiding timeline urgency for one whole tier, and it flagged 100+ properties still tied to a manager who had left, orphaned work no one had noticed. Both were fixed before the first leadership review, not after.
- Record counts reconciled across all three systems, end to end.
- Gate and tier totals matched the source within tolerance.
- Caught a display bug masking urgency on blocked records.
- Flagged 100+ orphaned properties for reassignment.
Outcome & impact
What changed.
One source of truth
The whole pipeline is ranked in one place, so "what should I work on?" has a consistent answer instead of a per-person guess.
Blocked ≠ urgent
Communities that can't be built no longer sit at the top of the list looking critical. Managers spend time only on what's actionable.
Self-updating
Change a gate in the source and the tier re-computes on the next refresh. No manual re-sorting, no stale rankings.
Freshness you can see
An on-report indicator flags when a morning data load failed, so no one acts on yesterday's numbers without knowing.
Delivered, not fetched
Each manager gets their own filtered report in their inbox every Monday, with no login, no filtering, and no dashboard hunting.
Data quality surfaced
The build itself exposed orphaned properties and a hidden-urgency bug that manual review had missed for weeks.
Skills demonstrated
What this shows.
SWITCH tiering · CALCULATE KPI measures · coalesce date logic · freshness detection
Power Query: internal field-name mapping & join-key debugging
Refresh scheduling around a daily file-replacement race condition
Independent data validation to verify a model before release
Power Automate: per-manager filtered PDF export & email
Translating fuzzy business rules into a deterministic scoring model
Weekly delivery
The report it sends.
Have data scattered across systems?
I turn disconnected trackers into one report people actually trust, with the logic, the refresh, and the delivery all handled. Tell me where your numbers live.