Your Semantic Layer is a Crime Scene

I opened the file the way you'd duck under a strip of police tape and within about thirty seconds, I knew I'd stumbled onto a crime scene.

Three measures named Revenue, Revenue_v2, and Revenue (do not use, ask Chris). A relationship between two tables set to many-to-many, with no comment explaining why. A calculated column doing, as far as I could tell, the exact job a relationship should have been doing — except the relationship wasn't there, so someone had brute-forced their way around it. I scrolled through forty-some fields with names like Calc1 and Temp_Join_Key and felt the particular dread of a person about to spend their afternoon doing forensics instead of analysis.

Then I checked who built it. I did. Ten months ago.

I am not investigating a stranger's crime scene. I am investigating my own, and I have no memory of committing the crime.

I didn't remember writing Revenue_v2. I didn't remember what was wrong with the original Revenue that made a sequel necessary. I had, at some point, made a series of decisions that felt completely reasonable in the moment — I was under deadline, I knew exactly what Temp_Join_Key meant, I was never going to forget it — and every one of those decisions had since evaporated from my memory, leaving only the evidence behind.

This is the thing nobody tells you about a messy semantic layer: it isn't a technical failure. It's a record. Every weirdly named column, every duplicated measure, every relationship nobody can explain is evidence of a decision somebody made under pressure, certain they'd remember the reasoning later. They didn't. You won't either.

And the next person to walk into that crime scene, the one squinting at your model trying to reconstruct what happened and why — statistically, that person is probably you.

Let me be clear about what the actual crime is here, because it's not what you think. Building Calc1 through Calc14, bolting on a third table just to test a theory, leaving a relationship many-to-many for an afternoon while you figure out if it even matters — that's not negligence. That's development in its purest form. Trial, error, and a healthy dose of innovation. Nobody arrives at a clean model without passing through an ugly one first, and if you're not occasionally making a mess while you work, you're probably not actually doing much analysis.

The crime is leaving the scene that way. Nobody who opens this file later — teammate, stakeholder, or future you — needs or wants to know how you got away with it. They don't want the trial and error preserved as a museum exhibit. They want the insight. They want a model that reflects the decision you eventually landed on, not an archaeological record of every decision you tried and discarded on the way there. The work of figuring it out is real and necessary. Letting it survive into the deliverable is the crime.

The Body: What Killed This Model

Every crime scene has a cause of death, and in a Power BI file this is a common theme: the model isn't a star schema, or it started as one and someone let it fracture into oblivion under deadline pressure.

You know the shape even if you've never heard the term. A fact table — your transactions, your events, the thing that happened — surrounded by dimension tables that describe it: date, customer, product, region. Clean, one-directional relationships radiating out from the center like points on a star. It's not academic purism. It's the difference between a model where Power BI's engine can do its job efficiently and one where every visual triggers a chain of ambiguous joins that someone, eventually, "fixes" with a calculated column that re-implements logic the relationships should have handled for free.

That fix is the moment of death. It works. It also means the model now contains undocumented business logic baked into a column instead of expressed in a relationship anyone could trace visually. Future you won't think to look there. Future you will assume the relationships tell the whole story, because that's what relationships are for.

You won't remember why you let the table structure unravel. You'll only remember that you did, vaguely, the way you remember finishing a marathon, but not mile seventeen.

Evidence Log — Star Schema
Briefly, the fact table holds your transactions/events. Dimension tables describe who, what, when, where. Relationships flow one direction, dimension to fact. If you find yourself defending a bidirectional relationship or a many-to-many out loud, that's usually the model telling you something's misshapen upstream — not a setting to quietly enable and move past.

The Loose Thread: The Hardcoded Connection String

Not every piece of evidence at a crime scene is the murder weapon. Some of it is just a loose thread — small, easy to miss, the kind of detail nobody thinks twice about until you pull it and the whole sleeve comes apart. The hardcoded connection string is one of those.

A server name typed directly into the Power Query source step. A database name baked into the M code. It works fine — right up until you need to move the report from dev to test to production, or your company migrates warehouses, or you simply forget which of three near-identical server names was the right one, and now you're hunting through Advanced Editor windows trying to find every place you typed it.

Ask me about the time I migrated an analysis server from the UK to the US and lost a weekend changing the database name in the source query for 55 reports. Every model I've built since has exactly one place that server name lives. It's clearly labelled, and it can be swapped online without opening Power BI Desktop or having to re-publish the file.

Hardcoding a connection value isn't a shortcut. It's a bet — a bet that you'll remember every place that value lives, and that nothing about your environment will ever change. Both halves of that bet tend to lose.

Evidence Log — Parameterize the Basics
Server name, database name, and environment-specific filters belong in Power Query parameters, not typed inline. Set it up once and every downstream query references the parameter instead of a hardcoded string. Promoting the report across environments — or just changing warehouses down the road — becomes a parameter update instead of an Advanced Editor scavenger hunt.

The Motive: Why Your Dataset and Your Report Live in the Same File

Ask anyone why their dataset and their report are jammed into a single pbix and you'll get some version of the same answer: speed. It's faster to build this way. You're in flow, the logic's all in your head, and splitting it into two files feels like overhead you don't have time for.

There's a quieter motive underneath that one, though, and it's the same one I've written about before in a different context: control. If the dataset lives inside your report, nobody else can touch it without touching your report. It's yours. Nobody can build something on top of it you didn't sign off on.

I understand the instinct. I've also watched it become a liability — both for the team, and for the version of me who comes back to this file later having forgotten which report was supposed to be the "real" source of the numbers and which was a one-off built for a single Tuesday meeting. A combined file is optimized for the person building it today. It is actively hostile to the person — teammate or future you — who opens it cold, with none of today's context loaded into memory.

Evidence Log — Separate the Dataset
A dataset published independently becomes a single source of truth that multiple reports can build on without forking the logic. It's slower on day one. It's also what buys you scale past day one.

The Alibi: Two Hundred Fields, No Folders, No Names That Mean Anything

Here's a strange thing about a file mid-build: it never looks suspicious to the person building it. You have two hundred un-grouped fields and names like Calc1 through Calc14, a bunch of database column names, like orig_hire_dt__c and none of it bothers you, because the context is sitting in your working memory in real time. You know exactly what Calc7 does. You don't need a folder to find it — you built it forty minutes ago.

And to be fair to Calc1 through Calc14 — they did their job. They were the scaffolding you needed to test an idea, rule out an approach, figure out which join actually held up. That's not the part anyone should feel bad about. The mistake isn't building Calc7. The mistake is shipping the file with Calc7 still in it, twelve siblings deep, none of them renamed or removed once they'd served their purpose. That's the equivalent of leaving the scaffolding bolted to the building after construction wraps and calling it architecture.

I've met analysts for whom losing the scaffolding is a real fear. What if I need to roll-back? How will I remember why I chose the final version? Let's agree to explain these details in your DAX comments and project documentation, and not as raw evidence scattered at the scene for someone else to dust for prints. A case file documents the investigation (Who was a suspect? What was ruled out? And Why?) without leaving the suspects themselves handcuffed to the furniture. Write down the decision and the reasoning. Then let the dead ends go.

All that working context has an expiration date. It doesn't survive the weekend, let alone ten months. The file that felt perfectly navigable while you were building it becomes a field list with no landmarks 10 days out. You're not looking for Calc7 anymore. You're looking for something, somewhere in here, that calculates the thing I need, and you have no faster way to find it than re-deriving what you already once knew.

The first time you figured it out, it was innovation. The second time you re-figured it out, it was opportunity cost.

This is the part of the crime scene that looks the most innocent and costs the most. Nobody hardcoded a password. Nobody broke a relationship. Somebody just never went back through and cleaned up after the experimenting was done — and as it happens, cleaning up was never a courtesy to a hypothetical teammate, it was a message in a bottle to themselves.

Evidence Log — Folders & Naming
Group measures and columns into display folders by subject (Sales, Dates, Customer) instead of leaving a flat alphabetical list. Name things for what they mean, not how you built them. "Net Revenue" survives; "Calc7" does not.

When you finish testing an approach, go back and delete the scaffolding, or rename and document what's left so it reads as a decision instead of a draft. If a measure needs a comment explaining a business rule, Power BI lets you add one — use it. You are writing this for someone with zero context, and that someone is, statistically, you.

What the Autopsy Should Have Found

Here's what I wish I'd opened ten months later instead: a fact table at the center, dimensions radiating cleanly outward. A dataset published on its own, with this report and two others quietly drawing from it. A field list organized into four folders I could scan in five seconds. A single parameter holding the server name, updated once when we migrated, without touching anything else.

None of that is impressive work. It's not the kind of build you screenshot for a portfolio. It's the kind of build that doesn't require an investigation later — which, it turns out, is the entire point. The trial and error that got me there happened anyway, off camera, the way it always does. Nobody needed to see it. Nobody needed to know how many Calc columns died so that Net Revenue could exist. By the way, that's Net Revenue not Revenue_v2, and you don't have to "ask Chris" because there are notes in the DAX Measure. This kind of clean-up isn't dishonesty — its just the difference between a draft and a finished product.

You are not building this for a teammate in the abstract, and you're not building it for some hypothetical future hire who inherits your work. You're building it for a specific person: tired, behind on a different deadline, with none of today's context left, opening this file because they have to. Most of the time, that person turns out to be you.

A clean semantic layer isn't about being fastidious, and it isn't about pretending you got it right on the first try. It's about covering your tracks once the experimenting is done — leaving a spotless crime scene that nobody else has to solve, including the future version of yourself who won't remember committing it.

So go ahead — commit your crimes. Build the fourteen Calcs. Let the model fracture for an afternoon while you chase down a theory. That's not the part I'm asking you to give up. Just bury the bodies and hire a cleaner before you hit publish. Build a step into the end of every dataset build — not a someday, not a "if I get time" — where you go back through and break out the bleach: delete what didn't survive, rename what did, fold the fields, set the parameters and build some relationship equity with future you.

It's the least exciting part of the work, but it's also the part that turns a one-off report into a dataset other people can actually build on top of. That's how you get from a single dashboard nobody else can touch to one clean dataset quietly powering twenty dashboards you didn't have to rebuild from scratch.

The crime scene is optional. The cleanup isn't.