People often ask me what actually happens between “here is an export from our ticketing system” and “here is your executive dashboard.” It is not a single step, and it is definitely not just opening Power BI and dragging fields onto a page. Here is the process I actually follow, in the order I follow it.

Step 1: Understand what the dashboard needs to answer

Before I open any tool, I ask whoever requested the dashboard a simple question: what decision will you make differently once you have this. If nobody can answer that, the dashboard is not ready to be built yet, no matter how urgent it feels.

This step also tells me who the audience actually is. An executive dashboard and a team lead’s operational dashboard look completely different, even if they pull from the same underlying data. Executives need trends, exceptions, and a small number of headline KPIs. Team leads need enough detail to act on individual queues or agents. Building one dashboard to serve both audiences usually ends up serving neither well.

Step 2: Get the raw export and actually look at it

Before touching Power Query or Excel formulas, I open the raw data and read through a sample of rows manually. I am looking for things like:

Inconsistent date formats or time zones across records
Duplicate rows (more common than people expect, especially from ticketing systems that log every status change as a new row)
Missing values in fields that will matter for the KPIs (no agent assigned, no resolution timestamp, no category)
Categories or statuses that overlap or mean the same thing under different labels

This step feels slow, but skipping it is exactly how you end up with a dashboard that has a wrong number on it three weeks after launch, and nobody notices until someone in a meeting asks a question the numbers cannot survive.

Step 3: Clean and shape the data in Power Query (or a proper ETL step)

Once I know what is wrong with the raw data, I fix it in a repeatable, documented way, not with one-off manual edits in Excel that nobody can reproduce next month. In Power Query, that usually means:

Standardizing date and time fields to one consistent format and time zone
Deduplicating based on a clear rule (usually keeping the latest status per ticket, or splitting into a separate status-history table if that history actually matters for reporting)
Mapping messy category or status values into a clean, consistent lookup table
Splitting the data into the fact and dimension tables the model will need (see my post on building a semantic model for the full reasoning behind this step)

I keep every transformation step named clearly in Power Query, so six months from now I, or anyone else, can open the query and understand exactly what happened to the data and why.

Step 4: Build the model before writing a single measure

This is the step people skip most often, and it is the one that causes the most rework later. I build out the star schema (fact table, date dimension, agent dimension, queue dimension, whatever the context needs) and I check the relationships before I write any DAX. If the model cannot cleanly support the KPI list from step 1, I fix the model now, not later.

Step 5: Write the core measures, starting with the simplest version

I build measures in layers. First the basic counts and sums (total contacts, total handle time), then the ratios built from those (AHT as handle time divided by contact count, service level as answered-within-threshold divided by total), then the more complex time-intelligence measures (month over month change, rolling averages, year over year comparisons).

Building in this order matters because it lets me verify each layer against a manual spot check before adding complexity on top of it. If the basic count is wrong, no amount of clever DAX on top of it will fix that, it will just make the mistake harder to find.

Step 6: Design the layout around the reading order, not the available space

Once the measures are solid, I think about layout. Executives read dashboards top to bottom, left to right, the same way they read a page. The most important number goes top left. Trends and context go next. Detail and drill-down go last, often on a separate page entirely.

I try to keep the main executive page to a small number of visuals, usually no more than 6 to 8, each answering one clear question. If a page needs a legend to explain what is going on, it usually needs to be split into two pages instead.

Step 7: Test the numbers against a source of truth

Before anything goes to an executive, I manually recalculate at least one or two KPIs directly from the raw export, using Excel formulas or a simple pivot table, completely independent from the Power BI model. If the two numbers do not match, I do not move forward until I understand exactly why.

This step has saved me more than once. It is also the step most people skip under deadline pressure, which is exactly when it matters most.

Step 8: Deliver it with a short explanation, not just a link

The dashboard is not finished when it is published. I always walk through it once with whoever requested it, explaining what each KPI means, how it is calculated, and what a good or bad number actually looks like in context. A dashboard nobody understands how to read gets opened once and then ignored.

Why the order matters

Every step in this process exists because I have seen what happens when it gets skipped. Skip step 2 and you inherit data quality problems that surface later as wrong numbers. Skip step 4 and you spend twice as long writing DAX that compensates for a bad model. Skip step 7 and the first person to catch your mistake is an executive in a meeting, not you at your desk.

None of this is complicated. It is mostly discipline, and doing the steps in the right order instead of jumping straight to the part that looks like progress.