“But it matches the ledger for October,” she said, tapping a fingernail against the edge of the mahogany table. “If October is right, why would November be wrong?”
“October isn’t right,” I told her, looking at the spreadsheet that had been masquerading as a database for . “October is just lucky. It’s a coincidence of data points that happened to hide the fact that your logic is hollow. You’re celebrating a bridge that didn’t collapse only because nobody drove a truck over it yet.”
Farah didn’t like that. Nobody likes being told that their success was a statistical fluke. , she sat at a corner desk in an office in Cyberjaya, facing her first real challenge with a semantic model. She had been tasked with creating a year-to-date calculation that would sync with the internal finance reports.
She wrote a measure, it returned a number, and that number matched the audited report sitting on her desk to the second decimal point. She felt the surge of dopamine that comes with solving a puzzle. She felt, quite reasonably, that she had mastered the rule.
The Trap of “Old Faithful”
That measure became her template. It was the “Old Faithful” of her digital toolkit. She copied its syntax into a dozen different files, across different departments, into reports that tracked everything from warehouse throughput in Klang to retail sales in Penang. For , the reports were accepted without question.
Then, in , a single unmatched row appeared in the source data-a transaction with a null date that somehow bypassed the initial cleaning phase. Suddenly, the year-to-date column began overstating the value of the entire portfolio by the exact amount of that orphan row, every single month, compounded.
The formula hadn’t changed. The data had. And because the formula was built on a fundamental misunderstanding of how DAX handles filter context, it had no internal mechanism to signal its own failure. It just kept returning numbers, confidently and incorrectly.
A flawed logic model remains invisible as long as the data stays within expected bounds.
In my work coordinating volunteers, I see a similar phenomenon. People arrive with a “formula” for how to talk to the grieving. They use it once, it seems to work, and they assume they have found a universal key. They don’t realize that the first person they spoke to was simply gracious enough to overlook their clumsiness.
The volunteer’s “success” was actually the patient’s patience. In data analytics, the “patience” of a clean dataset is the most dangerous thing a beginner can encounter. It allows you to build a career on a series of lucky guesses.
From Cell Thinking to Relationship Logic
The technical reality of Farah’s mistake is a classic case of row context transition. In the world of Power BI, there is a distinct difference between a calculation that looks at a single row and one that looks at the entire table through a set of filters.
When Farah wrote her first measure, she used a function that worked perfectly on a flat, single-table model. She didn’t realize that as soon as she moved that logic into a star schema-where data is split between “fact” tables of transactions and “dimension” tables of dates or products-the way the computer “thinks” about those numbers changes entirely.
Excel Logic
Physical addresses. Cell A1 + B1. The program looks at a house number on a street.
Semantic Logic
Contexts and Relationships. No addresses, only filters. If not explicit, the model makes assumptions.
She was using Excel-logic in a relational-engine world. In Excel, a cell is a physical address, like a house number on a street. You tell the program to look at A1 and add it to B1. In a semantic model, there are no addresses. There are only relationships and contexts.
If you don’t explicitly tell the model how to handle a relationship, it makes an assumption. For a year and a half, the model’s assumptions happened to align with reality. The moment the data became “messy,” the assumptions stayed the same, but reality shifted.
The Hidden Cost of the “Pity Number”
This is the “pity number.” A pity number is a result that is close enough to the truth that you don’t bother to audit the formula, but wrong enough to eventually cost someone their job or their budget. It is the result of feedback that is delayed and ambiguous.
If you touch a hot stove, the feedback is instant and unambiguous; you never “accidentally” learn the wrong rule about stoves. But if you write a flawed DAX measure, the “burn” might not happen for . By then, you’ve used that same flawed logic in fifty other reports. You’ve built a cathedral on a foundation of sand, and you’ve been decorated for the architecture.
In the Malaysian market, where digital transformation is moving at a breakneck pace, we see this often. Companies are eager to move away from manual spreadsheets, but they often bring their spreadsheet-thinking with them. They hire bright, capable people like Farah who are self-taught.
Being self-taught is a badge of honor until it becomes a ceiling. You can only “tinker” your way through so much complexity before the silent errors start to outpace your ability to catch them.
The transition from a “user” to an “analyst” requires a deliberate breaking of those early, false rules. It requires a clinical look at how data actually moves through a CPU. For example, understanding the “Expanded Table” concept is the difference between guessing why a total is wrong and knowing exactly which filter is being ignored. It’s the difference between being a passenger and being the mechanic.
Moving Beyond Guesswork
To move beyond the “guess and check” phase of report building, pursuing a
becomes a necessity rather than an elective.
The courseware provided by an authorized Microsoft Learning Partner like Trainocate Malaysia doesn’t just teach you where the buttons are. It forces a confrontation with the underlying logic of the PL-300 curriculum.
It moves the learner from “this formula returns the right number” to “this formula is architecturally sound.” This is a vital distinction. An architecturally sound formula will fail loudly if the data is corrupted, or better yet, it will account for the corruption and exclude it.
It doesn’t give you a pity number. It gives you the truth, even if the truth is “I can’t calculate this because the date table is missing a month.”
I spent an afternoon once trying to explain the internet to my grandmother. She thought the “Google” was a person sitting in a room answering her questions. She didn’t understand the infrastructure-the cables under the ocean, the server farms, the protocols. To her, the “magic” was enough.
But if the magic ever stopped, she would be helpless. Many data professionals are in the same position. They treat Power BI like a magic box that turns messy CSV files into pretty charts. As long as the charts look “right,” they don’t ask about the cables under the ocean.
The Accountability of “Magic”
But when you are responsible for the data that drives a supply chain or a hospital’s resource allocation, “magic” is a liability. You need to know the protocol. You need to know that your YTD measure isn’t just a string of text you found on a forum, but a specific set of instructions that handles the “AllSelected” filter and the “DateAdd” function with surgical precision.
Farah eventually had to go back and rebuild all twelve of her major reports. It took her of overtime. She had to unlearn the shortcut that had served her so well for so long. It was painful, like physical therapy for a limb that had healed incorrectly.
She had to admit to her manager that the numbers from the previous quarter were “directionally correct but technically invalid.” That is a hard sentence to say in a boardroom in Kuala Lumpur.
We often assume that expertise is the accumulation of “working” solutions. In reality, expertise is often the systematic discarding of solutions that only worked by accident. It is the realization that the first formula that worked for you wasn’t a gift; it was a trap. It bought your trust with a single correct result and then spent that trust over the next thousand calculations without you noticing the debt you were accruing.
The most dangerous moment in any professional’s career is the moment they stop asking “Why does this work?” because they are too busy celebrating the fact that it does. The “why” is where the safety lives. The “why” is what keeps the bridge standing when the heavy trucks finally start to roll across.
Whether you are managing a team of volunteers or a fleet of data analysts, the goal is the same: to build systems that are robust enough to survive the messy, unpredictable reality of the world, rather than systems that only work when the sun is out and the dates are clean.
If you are still using the first formula that ever worked for you, do yourself a favor. Go back to your first report. Add a null value. Change a relationship to many-to-many.
Watch what happens to the total. If it stays the same, you haven’t mastered a rule. You’ve just been lucky. And in the world of data, luck is a very expensive thing to rely on.