Building a Contact Center Power BI Dashboard

Contact center data punishes lazy modeling. It is high-volume, arrives at several different grains, has attributes that change over time, and gets compared across intervals constantly. A flat table that worked fine for a sales report will fall over here within a quarter.

This is the architecture that holds up.

1. Decide the fact grains first

Most contact center models need three fact tables at three different grains, not one table trying to serve all of them:

• Call fact — one row per call leg. Durations, timestamps, outcome, agent, campaign, queue, ANI, destination.
• Interval fact — one row per queue per 15 or 30 minute interval. Offered, answered, abandoned, answered-within-threshold, and the staffing figures. Service level is calculated here, never derived from the call fact.
• Agent state fact — one row per agent state change. Ready, not-ready with reason, ACW, talk. Occupancy and adherence come from here.

Trying to compute service level from call-level data is the most common modeling mistake in this domain. It quietly produces a number that is close enough to look right and wrong enough to matter.

2. Build conformed dimensions

Shared dimensions are what let the three facts be analyzed together:

• Date — a proper marked date table, covering full years, with fiscal attributes if the business uses them.
• Time of day — a separate dimension at interval grain. Do not put time attributes in the date table; it destroys the cardinality.
• Agent — a type-2 slowly changing dimension. When an agent moves team, historical calls must stay attributed to the team they were in at the time.
• Campaign, queue, disposition, ANI group — each with a surrogate key, and each with a documented grain.

The date and time-of-day split matters more than it sounds. A combined datetime dimension at minute grain across three years is over 1.5 million rows and will make every time-intelligence calculation slower than it needs to be.

3. Keep the model narrow

Contact center fact tables get wide fast. Practical rules:

• Drop columns you are not using. Every unused high-cardinality column costs memory and refresh time.
• Split datetime columns into a date key and a time key. VertiPaq compresses these far better than a full datetime.
• Avoid storing call IDs and GUIDs in the model unless drill-through genuinely requires them. If it does, isolate them in a separate DirectQuery detail table.
• Push transformation upstream. Power Query is for shaping, not for business logic that belongs in the warehouse.

4. Write measures, not calculated columns

A disciplined measure layer is what makes the model reusable. Some patterns specific to this domain:

Service level — aggregate correctly

Sum the numerator and denominator at the reporting grain, then divide. Never average a pre-calculated percentage:

Service Level % =
DIVIDE (
    SUM ( 'Interval'[AnsweredWithinThreshold] ),
    SUM ( 'Interval'[Offered] ) - SUM ( 'Interval'[ShortAbandoned] )
)

The short-abandon exclusion in the denominator is a business decision. Whichever way it goes, it belongs in one measure, documented, not re-decided per report.

AHT — a single definition

AHT =
DIVIDE (
    SUM ( 'Call'[TalkSeconds] )
        + SUM ( 'Call'[HoldSeconds] )
        + SUM ( 'Call'[ACWSeconds] ),
    [Handled Calls]
)

With Handled Calls itself a measure, so the transfer-counting rule lives in exactly one place.

Use calculation groups for time intelligence

Rather than writing prior-period and year-over-year variants of thirty measures, define the time-intelligence logic once as a calculation group. It collapses the measure count dramatically and makes the behavior consistent across every report page.

Name measures for humans

Adopt a convention and hold to it — AHT, AHT PY, AHT var %. Analysts building self-service reports will reuse what they can understand and rebuild what they cannot.

5. Refresh strategy

Call volumes make full refresh impractical quickly. Configure incremental refresh on the call and interval facts with:

• A rolling archive window sized to your reporting horizon, not to what the source retains.
• An incremental window long enough to absorb late-arriving and retroactively-edited records — disposition corrections are common and often land days later.
• Query folding verified end to end. If folding breaks in Power Query, incremental refresh silently degrades into a full scan.
• Detect data changes on a reliable modified-timestamp column where the source provides one.

6. Design the report around decisions

Three tiers, each with a job:

• Executive page. Six to eight measures, each with a variance against a stated comparison, and a one-line reason where the system can infer one. If a number is off-target, the page should make the next question obvious.
• Operational page. Interval-level detail for the people managing the floor today. Service level by interval, queue depth, staffing versus requirement, live SLA risk.
• Diagnostic drill-through. Campaign, agent, disposition and ANI breakdowns reached by drilling from the pages above — not sitting permanently on screen.

The most common executive-dashboard failure is putting the diagnostic content on the executive page. It produces a dense screen that no executive reads and no operator uses.

7. Govern it

• Separate development, test and production workspaces with a deployment pipeline.
• One certified dataset; reports connect live rather than importing their own copies.
• Row-level security modeled on the organizational hierarchy, with the roles tested before release.
• A documented reconciliation against platform-native reporting, run on a schedule, with known variances explained rather than merely tolerated.

What good looks like

A finished implementation should hit: executive page render under two seconds on cold cache, incremental refresh completing well inside its window, every measure traceable to a single definition in the KPI dictionary, and a new report buildable by an internal analyst without touching the model.

If those four are true, the estate will survive organizational change. If they are not, the next reorganization will start the rebuild.