What Is DAX? Measures, CALCULATE and Filter Context Explained

What Is DAX? Measures, CALCULATE and Filter Context Explained

DAX (Data Analysis Expressions) is the formula language used in Power BI, Excel Power Pivot and Analysis Services. It looks like Excel formulas, but it works on whole tables and columns instead of single cells. Most DAX is written as measures: calculations that recompute automatically for every filter, slicer and chart they appear in.

Most people who give up on Power BI give up at DAX. It isn't that the functions are hard. SUM and AVERAGE work the way you'd expect. The problem is that DAX evaluates formulas in a different way from Excel, and nobody explains that difference up front. This guide starts there.

Why DAX Matters

The drag-and-drop side of Power BI takes you a long way: connect to data, drop a field on a chart, and Power BI sums it for you. You hit the limit the first time a stakeholder asks for something like:

  • "Show margin %, not just revenue and cost."
  • "Compare this month to the same month last year."
  • "What share of total sales does each region make up, even when I filter to one region?"

None of these can be built by dragging fields. You need DAX. It's also a large part of the PL-300 exam: the "Model the data" area makes up 25–30% of the exam, and Microsoft's current skills list names CALCULATE, time intelligence, semi-additive measures and calculation groups directly. See our PL-300 exam guide for the full breakdown.

Measures vs Calculated Columns

This is the first decision you make every time you write DAX, and beginners usually get it wrong.

Calculated column Measure
Computed when Once, when the data refreshes Every time a visual is displayed or filtered
Stored in the model? Yes, it takes memory like any other column No, it's calculated on demand
Evaluated per Row (row context) Whatever the visual is showing (filter context)
Good for Categorizing rows, like Price Band = IF(Sales[Price] > 100, "Premium", "Standard") Aggregations: totals, ratios, % of total, year-over-year
Can go on a slicer or axis? Yes No

Rule of thumb: if you'd use the result to filter or group by, make it a column. If you'd use it as a number in a visual, make it a measure. In most well-built models, the large majority of DAX is measures.

Your First Measures

Assume a simple model: a Sales fact table with Amount, Cost, Quantity and Unit Price, connected to Date, Product and Region tables.

Total Sales = SUM ( Sales[Amount] )

Total Cost = SUM ( Sales[Cost] )

Total Profit = [Total Sales] - [Total Cost]

Margin % = DIVIDE ( [Total Profit], [Total Sales] )

Two habits worth building from day one:

  1. Reference measures inside other measures ([Total Sales]) instead of repeating SUM(...). When the definition of "sales" changes, you fix it in one place.
  2. Use DIVIDE instead of /. DIVIDE returns a blank instead of an error when the denominator is zero, so a region with no sales won't break your visual.

Filter Context: The Idea That Makes DAX Click

Put Margin % into a matrix with regions on the rows. The same measure returns a different number in every cell, and you never told it which region to use.

That's filter context: the set of filters active for a particular cell. For the "North" row, the filter context is Region = North, plus any slicer selections, page filters and report filters. The measure runs once per cell, inside that cell's filters.

This is the core difference from Excel. In Excel, a formula points at specific cells. In DAX, a measure describes how to calculate, and the visual decides what data it calculates over. Once that clicks, most of DAX follows from it.

Row context is the other kind of context. It exists when DAX is working through a table one row at a time, which happens in calculated columns and in "X" functions such as SUMX:

Revenue = SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )

SUMX multiplies quantity by price for each row and then adds up the results. That's why you use it when the calculation has to happen before the aggregation. A plain SUM can't do that.

CALCULATE: Changing the Filter Context

CALCULATE is the most important function in DAX. It evaluates an expression inside a modified filter context: it can add filters, replace filters or remove them.

Adding a filter:

West Sales = CALCULATE ( [Total Sales], Region[Region] = "West" )

This returns West's sales in every cell, whichever region the row shows.

Removing a filter to get "% of total":

Sales % of All Regions =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], ALL ( Region ) )
)

Worked example. Say North sold $400K, South $250K and West $350K, for a total of $1,000K. In the West row:

  • [Total Sales] is evaluated with the filter Region = West, so it returns $350K
  • CALCULATE([Total Sales], ALL(Region)) removes the region filter, so it returns $1,000K
  • The result is 350 ÷ 1,000 = 35%

The measure keeps working when someone filters the report to one product category, because ALL(Region) removes only the region filter and leaves the product filter in place. That selectivity is what makes CALCULATE so useful.

Time Intelligence: Year-to-Date and Year-over-Year

Time comparisons are the most common requests in business reporting, and DAX has built-in functions for them:

Sales YTD = TOTALYTD ( [Total Sales], 'Date'[Date] )

Sales LY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

Sales YoY % = DIVIDE ( [Total Sales] - [Sales LY], [Sales LY] )

If this year's sales in a period are $1.2M and the same period last year was $1.0M, Sales YoY % returns 20%.

The prerequisite everyone skips: time intelligence needs a proper date table. That means one row per day, no gaps, covering the full range of your data, and marked as a date table in Power BI. Relying on the automatic date hierarchy works for simple charts but causes confusing results once you write time intelligence measures. "Create a common date table" is its own line item in the PL-300 skills list for this reason.

Variables Make DAX Readable

Once measures get longer, use VAR and RETURN:

Sales YoY % =
VAR CurrentSales = [Total Sales]
VAR LastYearSales = [Sales LY]
RETURN
    DIVIDE ( CurrentSales - LastYearSales, LastYearSales )

Variables are computed once and reused, so they're often faster as well as easier to debug. You can temporarily RETURN any variable to check its value.

DAX vs Excel Formulas

Excel formulas DAX
Works on Cells and ranges (A2:A100) Tables and columns (Sales[Amount])
Result depends on The cells referenced The filter context of the visual
Lookups VLOOKUP / XLOOKUP Relationships in the model, plus RELATED
Conditional sums SUMIFS CALCULATE with filters
Typical mistake coming from Excel — Trying to point at "the cell above" instead of thinking in filters

If you're coming from Excel, the Excel vs Power BI comparison covers what else changes when you move over.

Common DAX Mistakes

  1. Making everything a calculated column. It works until the model gets large and slow, and columns can't respond to slicers the way measures do.
  2. Skipping the date table. Time intelligence then returns blanks or odd totals.
  3. Averaging a ratio. The average of each region's margin % is not the overall margin %. Calculate the ratio from totals (DIVIDE([Total Profit], [Total Sales])) so it's correct at every level.
  4. Leaving implicit measures in the model. Dragging a numeric column onto a visual creates a hidden auto-sum. Explicit measures are reusable, can be formatted consistently, and are what reviewers and exam questions expect.
  5. Wrapping a whole table in FILTER inside CALCULATE when a simple condition would work. CALCULATE([Total Sales], Region[Region] = "West") is clearer and usually faster than CALCULATE([Total Sales], FILTER(Sales, ...)) over the full fact table.
  6. Summing things that shouldn't be summed over time. Inventory levels and account balances are semi-additive: you can sum them across stores but not across months. They need "closing balance" logic, and that's also a named PL-300 skill.

Can Copilot Write DAX for Me?

Partly. Copilot in Power BI and general AI assistants can draft measures from a plain-English description, and they do well on standard patterns like YTD and % of total. The catch is that the output can be syntactically valid and still wrong for your model, for example by removing the wrong filter or ignoring your date table. You need to understand filter context to catch those errors. That's the same pattern we describe in Will AI Replace Data Analysts?: AI speeds up people who can check its work.

What to Learn Next

Frequently Asked Questions

Is DAX hard to learn? The basics (SUM, DIVIDE, simple CALCULATE) take a few days for anyone comfortable with Excel. What takes weeks is getting used to filter context. Expect a period where measures return numbers you didn't expect. Working out why is how you learn it.

Do I need to learn DAX to use Power BI? Not for simple reports. For anything a stakeholder will rely on (margins, comparisons, % of total), yes. And you can't pass PL-300 without it.

Is DAX the same as Power Query (M)? No. Power Query (the M language) shapes and cleans data before it loads into the model. DAX calculates on top of the loaded model. A good rule is to do cleaning and reshaping in Power Query and business calculations in DAX.

Where else is DAX used besides Power BI? Excel's Power Pivot, SQL Server Analysis Services and Azure Analysis Services, and Microsoft Fabric semantic models all use DAX. What you learn carries over across the Microsoft data stack.

Bottom Line

DAX is Power BI's calculation language. It's harder than Excel formulas for one reason: results depend on filter context, not on fixed cell references. Learn to separate measures from calculated columns, understand how CALCULATE changes the filter context, and build a proper date table. Those three things cover most of the DAX that real reports and the PL-300 exam need.

Enjoyed this article?

Share it with your network

Listings related to What Is DAX? Measures, CALCULATE and Filter Context Explained