DAX Basics in Power BI Desktop - What You Actually Need to Know First

Most people start using Power BI without writing a single DAX formula. You import some data, drag fields onto a canvas, and get a decent-looking report. That works fine until someone asks a question your drag-and-drop report can't answer. "What's the year-over-year growth by product category?" or "Show me the rolling 12-month average compared to budget." That's when DAX stops being optional.

DAX - Data Analysis Expressions - is the formula language that sits behind Power BI's calculation engine. If you've used Excel formulas, you'll recognise some of the patterns. But DAX has its own quirks, and the mental model is different enough that Excel habits can actually trip you up.

Microsoft has a thorough introduction to DAX basics that covers the syntax and core functions. I want to build on that with what we've learned from training teams across Australian organisations on DAX - the stuff that clicks, the stuff that confuses people, and the patterns that actually matter day one.

Why Bother Learning DAX at All?

Fair question. Power BI does a lot out of the box. Implicit measures (when you just drag a numeric field and it auto-sums) handle basic scenarios without any formula writing.

But implicit measures are a trap. They work until they don't, and when they stop working, you have no control over what went wrong. We've seen reports at mid-size organisations where nobody could explain why a KPI card showed a particular number because it was built entirely on implicit measures and auto-summarisation. Debugging that is painful.

Writing explicit DAX measures gives you two things: control and reusability. You define exactly what a calculation does, and you can reference that measure in other measures. A Total Sales measure becomes a building block for Sales Growth %, which feeds into Sales vs Budget Variance. That composability is the real power of DAX.

The Three Things That Actually Matter

Microsoft's documentation talks about syntax, functions, and context. That's the right framework. But let me explain what each of these means in practice, because the docs are accurate without being particularly helpful about where people get stuck.

Syntax - It's Simpler Than It Looks

A DAX formula looks like this:

Total Sales = SUM(Sales[SalesAmount])

That's a measure called "Total Sales" that sums the SalesAmount column from the Sales table. The syntax pattern is always: MeasureName = SomeExpression.

The bit that trips people up is the table reference. Sales[SalesAmount] means "the SalesAmount column in the Sales table." You can drop the table name if you're already in that table, but don't. Always qualify your column references with the table name. Your future self will thank you when you're staring at a formula six months from now trying to figure out which Amount column it's pulling from.

One gotcha: if your table name has spaces or special characters, wrap it in single quotes. 'Sales Data'[SalesAmount] not Sales Data[SalesAmount]. We generally advise clients to avoid spaces in table names altogether. It saves headaches.

Functions - Start With Five

DAX has hundreds of functions. Ignore most of them for now. In our Power BI consulting work, we find that five functions cover roughly 80% of what people need when they're starting out:

SUM - Adds up all values in a column. Dead simple.

CALCULATE - This is the big one. CALCULATE takes an expression and applies filters to it. Want total sales but only for the current year? That's CALCULATE. Want total sales but only for a specific region? Also CALCULATE. Almost every interesting DAX formula uses CALCULATE somewhere in the chain.

FILTER - Returns a table that's been filtered to specific rows. Often used inside CALCULATE to create more complex filter conditions.

COUNTROWS - Counts rows in a table. More reliable than COUNT (which counts non-blank values in a column) when you actually want to know how many records exist.

DIVIDE - Divides two numbers but handles division by zero gracefully. Use this instead of / operator when there's any chance the denominator could be zero. It returns blank (or a value you specify) instead of throwing an error.

That's it. SUM, CALCULATE, FILTER, COUNTROWS, DIVIDE. You can build a surprisingly useful set of business metrics with just these five.

Context - Where Everyone Gets Confused

This is where DAX gets genuinely hard, and it's worth spending time here because misunderstanding context is responsible for most of the "my numbers are wrong" issues we see.

There are two types of context in DAX:

Row context exists when DAX evaluates a formula row by row. Calculated columns always have row context - they compute a value for each row in the table. When you write Sales[Revenue] = Sales[Quantity] * Sales[UnitPrice], DAX knows to use the Quantity and UnitPrice from the current row.

Filter context is what slicers, filters, and visual groupings create. When you put a chart on a report that groups sales by region, each bar in that chart has a different filter context - one where Region = "NSW", one where Region = "VIC", and so on. Your measures are evaluated once per filter context.

The confusion happens because measures and calculated columns behave differently with context. A measure doesn't have row context by default. If you write a measure that references Sales[UnitPrice], DAX doesn't know which row's unit price you mean. It needs a filter context to narrow things down, or an aggregation function like AVERAGE to combine all the values.

Here's a real example that comes up often. A client wanted to flag orders where the line amount exceeded $10,000. They wrote a measure:

Large Orders = IF(Sales[Amount] > 10000, "Yes", "No")

This doesn't work as a measure because there's no row context. DAX doesn't know which row's Amount to check. The fix is either making it a calculated column (which has row context) or restructuring the logic to use aggregation:

Has Large Orders = IF(MAX(Sales[Amount]) > 10000, "Yes", "No")

Understanding the difference between row context and filter context is the single most valuable thing you can learn about DAX. Everything else builds on it.

CALCULATE - The Function That Runs Everything

I mentioned CALCULATE above, but it deserves its own section because it's both the most powerful and most misused function in DAX.

CALCULATE does one thing: it evaluates an expression in a modified filter context. The first argument is what you want to calculate. Every argument after that is a filter modification.

Previous Quarter Sales = CALCULATE(SUM(Sales[SalesAmount]), PREVIOUSQUARTER(Sales[OrderDate]))

This says: take the sum of SalesAmount, but change the filter context so that only rows from the previous quarter are included. The time intelligence function PREVIOUSQUARTER handles figuring out what "previous quarter" means relative to the current filter context.

Where people go wrong with CALCULATE is stacking too many filters without understanding how they interact. Multiple filter arguments in CALCULATE use AND logic between different columns, but they replace existing filters on the same column. That replacement behaviour catches people out constantly.

Say your report is filtered to show 2025 data, and you write:

All Years Sales = CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales[Year]))

The ALL function removes the existing year filter. So even though the user selected 2025 in a slicer, this measure shows the total across all years. That's useful for "percentage of total" calculations, but it confuses people who expect filters to be additive.

Calculated Columns vs Measures - Pick the Right One

This is a decision you'll face constantly, and getting it wrong creates real problems. Here's the practical rule:

Use a calculated column when you need to add a derived value that each row should have. Things like categorising transactions ("Small", "Medium", "Large"), combining first and last names, or calculating a per-row margin. Calculated columns are computed once at refresh time and stored in the model, so they increase your model size.

Use a measure when you need a value that changes based on filter context. Year-to-date totals, averages that respond to slicers, percentages of total - these should be measures. Measures are computed on the fly and don't add to model size.

The mistake we see most often is people using calculated columns where they should use measures. Someone creates a calculated column for "Sales Growth %" that computes a static value per row, then wonders why it doesn't change when they filter by region. Of course it doesn't - it's a stored value, not a dynamic calculation.

Common First-Week Mistakes

After running DAX training sessions for dozens of teams through our business intelligence consulting, these are the mistakes that come up in almost every session:

Mixing up SUM and SUMX. SUM adds up a column. SUMX iterates over a table and evaluates an expression for each row, then sums the results. If you need to multiply quantity by price for each row and then sum, that's SUMX, not SUM.

Ignoring blank handling. DAX treats blanks differently from zero. A blank in a sum doesn't add anything, but a blank in a multiplication makes the whole result blank. Use COALESCE or IF(ISBLANK()) to handle these cases explicitly.

Writing overly complex formulas. If your DAX expression is more than about five lines, break it into smaller measures. Create intermediate measures with clear names. Net Revenue = [Gross Revenue] - [Discounts] - [Returns] is much easier to debug than one massive formula that does all three calculations inline.

Not testing with known data. Always verify your DAX formula against a small dataset where you can calculate the expected answer by hand. If you can't explain why a formula returns 47,832.50, you don't understand it well enough to ship it.

Getting Started Without Getting Overwhelmed

My advice for anyone picking up DAX for the first time: don't try to learn it all at once. Start by converting your implicit measures to explicit ones. Wherever your report uses auto-sum, create a proper measure with SUM. That alone will make your reports more predictable and easier to maintain.

Then pick one business question your current report can't answer and figure out the DAX for that specific thing. "What percentage of total sales does each product category represent?" is a great first challenge because it forces you to learn CALCULATE and ALL.

From there, time intelligence functions (year-over-year, rolling averages) are usually the next step because business users always want trend comparisons.

If you're working on Power BI models for your organisation and want guidance on getting your DAX patterns right from the start, the Team 400 Power BI consulting team has helped plenty of Australian businesses build models that actually scale. Getting the foundations right early saves a lot of rework later.

DAX isn't hard once the mental model clicks. The syntax is straightforward. The function library is well-documented. It's filter context that takes practice - and the only way to get that practice is to write formulas, break things, and figure out why. That's how everyone learns it.