From support ticket to dashboard

I spent three years in technical support. That means reading other people's systems, under time pressure, from a description that rarely matches the actual problem. Do that long enough and you develop a particular reflex. Don't guess. Reproduce first, then narrow down, then touch anything.

That reflex is what helps me most with data models today. A broken measure is just another ticket: somebody says the number is wrong. What they mean is something you have to work out.

What three years in support actually teach you

The reflex has three parts, and all three carry over to data work directly:

• Reproduce before you repair. If I can't show when the number goes wrong, I also can't tell whether I fixed it.
• The question behind the question. "Revenue is too low" sometimes means returns are missing, and sometimes that somebody has a different period in mind than the filter pane does.
• One change at a time. Adjust three things at once and it starts working again, and you still don't know why.

None of that is spectacular. It is, however, the difference between a model that happens to work and one whose numbers you can trust.

The mistake I keep running into

When a report lands on my desk because the year-on-year comparison is off, it is almost always the same thing: there is no dedicated date table, or it hasn't been marked as one.

This measure looks correct at first glance:

Revenue PY =
CALCULATE (
    [Revenue],
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)

It still returns blanks or wrong totals as soon as one of three conditions is missing. The 'Date' table needs a continuous run of dates, it has to be marked as a date table in Power BI, and — the one most often overlooked — the visuals have to filter through the date table, not through the date column on the fact table.

That last point explains the classic symptom: everything checks out in your test visual and falls apart in the finished report. Time intelligence functions work on the filter context of the date table. If the filter arrives from somewhere else, they have nothing to hold on to.

Why a star schema isn't bureaucracy

The second recurring problem is models where everything connects to everything. Usually somebody once set a relationship to bidirectional because a single filter wouldn't propagate. That one filter now works — and three other numbers are quietly broken.

A star schema isn't a formality. It's a decision about which way filters are allowed to flow: from the dimensions to the fact table, one direction, no detours. That makes results unambiguous, and faster as a side effect.

And the cheapest place to shape data that way isn't Power BI, it's the database. Whatever I aggregate there never has to travel through the engine later:

SELECT
    d.year_month,
    SUM(f.amount) AS revenue
FROM   fact_sales AS f
JOIN   dim_date   AS d
       ON d.date_id = f.date_id
GROUP  BY d.year_month;

What SQL can't do, I do in Power Query. What Power Query can't do either ends up in DAX. Keeping that order saves more time in the long run than any optimisation trick applied to a finished report.

Why I document this in public

Two reasons. First, I learn faster when I write something down so that somebody else could follow it. Half-formed explanations don't survive that test.

Second, it's the most honest reference I can offer. Anyone thinking about working with me can read how I approach a problem, instead of scanning a list of buzzwords on a profile page.

What's coming next

• Building a date table by hand — and why I'd rather generate it in SQL than in DAX
• Power Query: the steps I run first on every messy export
• Cleaning up a model that grew organically, without breaking the existing report

If a question comes up along the way, or you have a model behaving strangely, write to me. The most interesting posts come out of real cases.