DAX Is Where Dashboards Quietly Break

A measure recomputes, a column stays frozen

A calculated column runs once, row by row, at the moment your data refreshes, and the answer it lands on gets stored in the table like any other field. That makes it the right tool for a value that truly belongs to each row. A line total of quantity times price, a flag that says this order is late, a clean category name you rebuilt from a messy one. You compute it per row, it sits there, and you can slice and group by it later.

A measure is a different animal. It holds no stored value at all. It runs live, at the second you look at it, over only the rows the current filter has left visible, and then it throws that answer away and does the whole thing again the next time anything changes. That makes it the right tool for anything you add up or compare. A total, an average, a ratio, a running number. My mistake on that sales card was asking a frozen column to behave like a live measure, and a frozen thing cannot react to a click.

The one line test

Before you write either one, finish this sentence. Will I put this on an axis, or slice and group by it? Then it wants to be a column. Will I drop it in a card, or read it as a total at the bottom of a table? Then it wants to be a measure. Ninety percent of the wrong numbers I have chased came from getting that one sentence backward.

The invisible thing every measure stands under

Filter context is just the set of filters that are switched on at the exact moment your measure runs, and it is quieter than people expect, because most of it is not a slicer you clicked. Every slicer, yes, but also every row of a table visual, every field sitting on an axis, every filter on the page, they all pile up into one invisible answer to a single question. Which rows am I allowed to see right now.

Take the same measure, one Total Sales, and put it in two places. Drop it on a card alone and it sees the whole company, so it sums every row. Put that identical measure into a table with one row per region and it runs again and again, once for each region, and every single time it sees only that region's rows. You did not touch the formula. You changed the context around it, and the number followed.

A measure does not have a value. It has a value per context.

Once that clicks, half of DAX stops feeling like magic. A measure is a small machine that asks who is visible, adds them up, and forgets. The whole skill is learning to reshape who is visible on purpose.

The two you will reach for again and again

Almost every dashboard I build ends up needing the same two moves, so it is worth understanding what they really do rather than pasting them blind. Both are the same trick underneath. You reach in and reshape the filter context on purpose, then run a normal total inside the shape you built.

A running total

You want sales that add up as the year goes, so May shows January through May, not May on its own. The problem is that the filter context at the May row only lets your measure see May. So you widen it. You take the latest date the current context is pointing at, then tell the total to ignore the month filter and keep every date up to and including that one. That reshaping is the whole pattern. The function that lets you rewrite the filter this way is CALCULATE, and it is the one word most of DAX is quietly built on.

Same month last year

You want this May sitting next to last May so the change is obvious. You take whatever date the current context holds, shift the window back by exactly one year, and run the same total under that shifted window. DAX has a small family of time functions for this, and they feel like magic right up until they hand you a blank for no reason. The reason is almost always the same, which brings me to the one thing you have to get right before any of it works.

How to find the wrong number, fast

When a number comes out wrong, and it will, resist the urge to rewrite the formula first. The formula is usually fine. The context it is running under is the liar. So I walk through the same short list every time, and it finds the problem in minutes.

First, click a slicer and watch. If the number does not react at all, you are almost certainly looking at a column where you needed a measure, and no amount of formula tweaking fixes that. Next, strip the measure bare on its own card, then add your filters back one at a time and watch each one. The filter that breaks the number is the context you misjudged. Then check it against one total you already trust, a quick manual sum in Excel for a single region, because if your DAX and your gut disagree, one of them is wrong and it is worth knowing which. And if a time comparison comes back blank, go straight to the date table before anything else.

That list will carry you a long way, and if you are learning DAX to become the person who builds these yourself, I would genuinely rather you learn it than pay anyone for it, the same way I would rather you pick up scripting than outsource it, which is why I wrote about how I learned to build fast with a little help from AI. But there is a point where the model grows, the measures start leaning on each other, and one wrong context quietly throws off a headline number the whole company reads every morning. That is the kind of thing a real executive dashboard lives or dies on. When the report matters that much and the deadline is real, that is the work I take on in my evenings around the day job at Huawei. If a dashboard of yours is showing a number you cannot fully trust, start a project and send me the file, and even if the honest answer is one measure and no project at all, I will tell you that plainly.