Just Because It Works Doesn't Mean It's Right

Modern AI tools can write impressive DAX, SQL and Power Query in seconds. They often produce code that returns exactly the result you asked for.

The problem is that they only answer the question you ask.

They know nothing about your semantic model, your storage engine, your Microsoft Fabric capacity, your refresh strategy, your business volumes or the hundreds of reports that share the same environment.

That responsibility still belongs to the person pressing Enter.

Recently I investigated a Microsoft Fabric environment that was regularly exceeding 200% capacity utilisation. Reports were timing out, refreshes were slowing down and nobody could understand why.

The culprit wasn't an obvious mistake.

It wasn't a runaway refresh.

It wasn't a badly configured capacity.

It wasn't even incorrect DAX.

The code worked perfectly.

That was the problem.

The most dangerous code is often technically correct

One of the biggest misconceptions in Business Intelligence is that correctness equals quality.

If a measure returns the expected value, we naturally assume the implementation must also be good. After all, users only see the final number. If Finance receives the correct number of working days or Sales receives the correct revenue figure, the solution appears successful.

From a business perspective, the problem has been solved.

From an architectural perspective, the real questions have barely started.

Every piece of DAX executes inside a wider system.

That system has memory constraints.

It has CPU limits.

It has refresh windows.

It has concurrent users.

It has storage engine operations.

It has query plans.

It has other reports competing for the same resources.

A single calculation that seems insignificant on its own can become extraordinarily expensive when repeated hundreds of thousands or even millions of times.

This is where AI becomes both incredibly useful and potentially dangerous.

Large language models are exceptionally good at recognising patterns.

Ask them how to calculate working days between two dates and they'll almost certainly give you a correct answer.

Ask them how to rank customers.

They'll do it.

Need a rolling twelve month calculation?

No problem.

Need a running total?

Done in seconds.

What they cannot do is ask the questions that experienced consultants instinctively ask before writing a single line of DAX.

How large is the table?

Is this Import mode or Direct Lake?

How often does it refresh?

How many users share this capacity?

Could this calculation be performed earlier in the pipeline?

Would Power Query be more appropriate?

Could the model itself be redesigned?

Those questions require context.

AI has none unless you provide it.

"AI can write code that answers your question. It cannot tell you whether you asked the right question in the first place."

Working is not the same as well designed

Technically Correct

Code that produces the expected output regardless of how efficiently, maintainably or scalably it achieves that result.

Over the past decade I've reviewed countless Power BI models where every calculation technically worked.

Some refreshed for hours.

Some consumed enormous amounts of memory.

Some prevented incremental refresh from delivering its benefits.

Others made every report slower despite appearing completely harmless.

None of these problems were visible to the report consumer.

They were hidden inside the architecture.

A real investigation

Recently I investigated unexplained Microsoft Fabric capacity usage that regularly exceeded 200%.

Nothing obvious stood out.

The semantic model wasn't unusually large.

No massive refreshes were taking place.

Visual performance looked acceptable.

Yet capacity spikes continued to appear throughout the day.

Eventually the investigation narrowed down to a single calculated column that had been introduced to calculate working days for each record. The business requirement itself was entirely reasonable.

The implementation looked reasonable too.

After anonymising the table and column names, the calculation looked broadly like this.

Working Days =
VAR StartDate =
    FactAssignments[Start Date]

VAR EndDate =
    COALESCE(
        FactAssignments[End Date],
        TODAY()
    )

RETURN
CALCULATE(
    COUNTROWS(DimCalendar),
    DimCalendar[Is Working Day] = TRUE(),
    DATESBETWEEN(
        DimCalendar[Date],
        StartDate,
        EndDate
    )
)

If you've written DAX before, there's a good chance this looks perfectly acceptable.

In fact, if you asked an AI assistant to calculate working days between two dates, there is a very good chance it would generate something remarkably similar.

That doesn't make the AI wrong.

It answered the question that was asked.

Calculate working days between two dates.

Mission accomplished.

Unfortunately, that wasn't the real question.

The real question should have been something entirely different.

How can we calculate working days between two dates in a semantic model containing hundreds of thousands of rows, running inside a shared Microsoft Fabric capacity, with refreshes occurring every day, while minimising compute cost and preserving long-term scalability?

Those are completely different questions.

One produces working code.

The other produces good architecture.

The hidden cost wasn't the calculation. It was the repetition.

When people first look at the DAX, they often focus on the DATESBETWEEN() function. Surely that's the expensive part?

Not really.

The individual calculation isn't particularly demanding.

The problem is that Power BI isn't executing it once.

It's executing it once for every row in the table.

That distinction changes everything.

Imagine a fact table containing 500,000 placement records.

Each row contains a start date and, optionally, an end date.

For every one of those rows, the calculated column creates a new filter over the calendar table, identifies every date between the two values, filters out weekends and finally counts the remaining rows.

Then it repeats the entire process.

Half a million times.

Nothing is technically wrong.

It's simply an enormous amount of repeated work.

Small calculations become massive workloads

One of the biggest mistakes people make when estimating performance is looking at a single execution.

If one calculation takes a fraction of a millisecond, it feels insignificant.

Unfortunately, computers don't care how small an individual operation is.

They care how many times it happens.

Suppose your calendar contains ten years of dates.

That's roughly 3,650 rows.

Now imagine Power BI evaluating those dates for every placement record.

500,000 rows multiplied by approximately 3,650 possible dates represents well over a billion date comparisons during model processing.

Not because anyone intentionally wrote inefficient code.

Simply because the chosen approach forces Power BI to perform the same work repeatedly.

The explanation accompanying the original investigation estimated that a model of this size could perform around 1.8 billion date comparisons during refresh.

The innocent looking TODAY() function

One line in particular caught my attention.

COALESCE(
    EndDate,
    TODAY()
)

There's nothing inherently wrong with using TODAY().

In many situations it's exactly the right choice.

The issue is the context in which it's being used.

Every active record now grows longer every single day.

A placement that started last week barely changes the workload.

A placement that started six years ago tells a very different story.

Every refresh requires Power BI to consider another day's worth of dates.

Tomorrow it becomes slightly more expensive.

Next month it becomes more expensive again.

Next year, more expensive still.

Performance quietly degrades over time without anyone touching the code.

Those are the hardest problems to identify because nothing appears to have changed.

1. Day 1

   Calculated column performs acceptably.

2. Six months later

   More active records increase processing.

3. Two years later

   Business growth magnifies the workload.

4. Four years later

   Capacity spikes begin appearing.

5. Investigation

   Everyone asks what recently changed. The answer is often "nothing".

Calculated columns aren't free

Another misconception is that calculated columns are somehow "cached", making them inexpensive.

They are cached after they've been calculated.

Getting them there is the expensive part.

During every model refresh Power BI has to evaluate every calculated column for every row that requires processing.

Read the row.

Execute the expression.

Store the result.

Move to the next row.

Repeat.

Unlike a measure, which is calculated when needed, a calculated column becomes part of the model itself.

That makes calculated columns extremely useful when they're the right tool.

It also makes them incredibly expensive when they perform complex row-by-row operations across large fact tables.

Why AI couldn't have warned you

This is where the conversation moves beyond DAX.

Many people frame discussions about AI as though the technology itself is the problem.

It isn't.

The problem is assuming AI has knowledge it simply doesn't possess.

When you ask an AI assistant to calculate working days between two dates, it doesn't know whether your table contains 500 rows or 500 million.

It doesn't know whether you're refreshing once a month or every fifteen minutes.

It doesn't know whether your model sits on a dedicated Fabric F256 capacity or shares a smaller capacity with dozens of other workloads.

It doesn't know whether this calculation will execute once or several million times.

Those details fundamentally change what "good" looks like.

Without them, the AI can only optimise for one objective.

Producing a correct answer.

"Artificial intelligence answers the question you ask. Experience questions whether you should be asking it at all."

The better question

Experienced consultants rarely begin with code.

They begin with architecture.

Before writing a calculated column, they ask whether the calculation belongs in DAX at all.

Could the source system provide the answer?

Could Power Query calculate it once during ingestion?

Could the warehouse generate it?

Could a cumulative working day index remove the need for repeated calendar scans?

Could the model itself be redesigned so the calculation disappears entirely?

Those questions often eliminate the problem before the first line of DAX is ever written.

And that's something AI still cannot do on its own.

It doesn't understand your architecture.

You do.

A better solution starts with a different mindset

One of the easiest traps to fall into is asking, "How do I write this in DAX?"

That question assumes DAX is the correct place to solve the problem.

It might be.

It might not.

The most effective Business Intelligence professionals don't think in terms of languages first. They think in terms of where work should happen.

If data can be transformed once during ingestion, why calculate it every refresh?

If a value never changes, why recalculate it?

If a lookup can replace thousands of calculations, why keep calculating?

Architecture is often about removing work rather than making computers perform it more efficiently.

What I would have done instead

There isn't a single universally correct solution because every organisation has different constraints.

However, there are approaches that scale significantly better than performing a date-range scan for every row in a large fact table.

The first option would be to question whether the calculation belongs in the semantic model at all.

If the source system already knows the duration of an assignment, use it.

If the ETL process can derive the value once, calculate it there.

If the business requires working day calculations that may change because of public holidays or regional calendars, consider whether those rules belong in the data platform rather than every semantic model built on top of it.

The semantic model should enrich data, not become the primary processing engine.

When DAX isn't the best tool

Power Query is often a much better home for calculations like this.

Unlike DAX calculated columns, Power Query transformations occur as part of the data preparation process. The engine is designed for large-scale data transformation and can often perform these operations far more efficiently.

Even better is moving the logic further upstream into a warehouse or Lakehouse where transformations become part of your data engineering pipeline.

Each stage moves the work closer to where it naturally belongs.

Instead of asking Power BI to repeatedly derive information, you deliver information that has already been prepared.

That distinction becomes increasingly important as your organisation grows.

Sometimes the fastest calculation is no calculation at all

One optimisation discussed during the investigation was using a cumulative working day index.

Rather than counting every individual working day between two dates, the calendar stores a running total of working days.

A simplified example might look like this.

Instead of scanning every date in the range, the calculation becomes little more than:

End Working Day Index
-
Start Working Day Index
+
1

The complexity changes from repeatedly evaluating date ranges to performing two lookups and simple arithmetic.

That is the kind of optimisation that fundamentally changes scalability.

AI isn't replacing expertise. It's amplifying it

Despite everything in this article, I use AI every day.

I use it to brainstorm.

I use it to explain unfamiliar functions.

I use it to review DAX.

I use it to generate SQL.

I use it to challenge my own thinking.

It makes me significantly more productive.

But I never mistake speed for certainty.

Every piece of generated code is treated exactly as if a junior developer had written it.

I assume positive intent.

I assume it might contain something useful.

I also assume it needs reviewing.

Because ultimately my name, not the AI's, sits against the production solution.

That's the mindset organisations need to develop.

AI should reduce repetitive work.

It should accelerate learning.

It should help experienced professionals explore ideas more quickly.

It should not become a substitute for understanding the platform you're responsible for designing.

The fundamentals haven't become less important because AI arrived.

They've become more important.

"The people who benefit most from AI are usually the people who could have solved the problem without it."

Final thoughts

The investigation that inspired this article wasn't caused by bad intentions.

Nobody deliberately tried to overload a Microsoft Fabric capacity.

The developer wanted to calculate working days.

The calculation returned the correct answer.

The report worked.

Unfortunately, production systems don't judge code purely by correctness.

They judge it by how efficiently it reaches that answer.

Every calculation has a cost.

Every design decision has consequences.

Every shortcut eventually collects interest.

AI is one of the most significant advances our industry has seen in decades, and every Business Intelligence professional should learn how to use it effectively.

But effectiveness comes from understanding enough to recognise when the answer is good, when it needs improving and when the question itself should have been different.

The next time an AI assistant gives you a piece of DAX that works perfectly, don't stop there.

Ask yourself one more question.

Would I still write it this way if my model contained a billion rows?

If the answer is no, you've just started thinking like an architect.