What DAX actually does: three levels in every Power BI report

Every Power BI course starts the same way. Someone asks, usually on day one: “Why doesn’t this DAX formula work the way it does in Excel?” The answer almost never lives in the formula itself. It lives one level up, in the question of which part of your report is supposed to be doing that work.

That question sounds simple, but it touches the biggest stumbling block in the Power BI learning curve: most beginner pain does not come from complicated functions. It comes from mixing up three clearly separate activities. Once you can tell those three apart, most DAX confusion becomes a lot more manageable.

Three activities, three tools

A Power BI report is built around three clearly separate activities, each with its own location, its own language, and its own job.

Getting and preparing data happens in Power Query, using a language called M. This is where data comes in from sources like Excel files, databases, or APIs, and gets shaped for use: converting columns, splitting fields, cleaning values, removing empty rows, merging or splitting tables. Power Query is the mise-en-place of your report. What you get right here, you do not need to fix later in DAX.

Modelling happens in the data model itself. You connect tables with relationships, keep facts and dimensions separate, and build a star schema: one central fact table with dimension tables around it, each describing one aspect of your data, such as customer, product, or date. A well-built model is the foundation that makes your calculations work without workarounds.

Calculating happens in DAX: summing, counting, counting under conditions, calculating ratios, producing every number that each visual and each filter needs to show. DAX does not work on rows you point to yourself. It works on the filter context the report has at that moment. That is exactly why the same formula in Excel and in DAX can produce different results: Excel works with what is in the cell, DAX works with what the report currently sees.

Measures and calculated columns: both in the third level

Both measures and calculated columns are DAX, but they behave very differently.

A calculated column is computed when your model refreshes. The result is stored row by row in the table, as an extra column that was always there. That works well for a category label that is fixed at row level, such as a customer tier based on revenue, but it takes up storage space because every value is physically saved in the model.

A measure is only calculated at the moment someone looks at your report, per visual, per filter, per row of the matrix. The result is stored nowhere: Power BI computes it fresh each time, based on the current context. That makes measures light and flexible, but also harder to understand if you come from cell-based thinking in Excel.

The practical difference: a calculated column built for something that changes by filter or by visual will always return the same value, regardless of context. A measure follows that context precisely. If you reach for a calculated column where a measure belongs, your model grows larger without becoming smarter. You will notice that in model size by day two.

Level first, function second

For every DAX question, the first question is a level question: which of the three activities is supposed to solve this.

If you want to clean or split a text column, check whether that belongs in Power Query. You work with M there, not DAX, and the result sits ready for the model. If a total looks wrong in your report, check whether all the relationships in your model are set up correctly before you start adjusting the measure. If you are building a calculated column for something that changes per filter or per visual, ask whether a measure is the better place for it.

That sounds like extra thinking, but it saves time overall. Debugging a DAX formula whose real problem sits in the model or in Power Query is considerably harder than handling it at the right level from the start.

Time to practise the level question. Six situations from daily practice; for each card, click the level that should solve it.

What this gives you as a foundation

Day one of this DAX series is not a syntax lesson. It is an orientation: three levels, three tools, three responsibilities. With that frame in place, the follow-up questions about measures and calculated columns, about SUM and COUNTROWS, about filter context, move much faster.

This is part one of five. Day two: when to use a measure, when to use a calculated column, and why that choice can make your model half the size or twice as large.

Want to work through the DAX foundation together? Visit trainerbjorn.nl for more on the training options, or get in touch directly.