Complete DAX Guide for Power BI: Beginner to Advanced Guide

DAX (Data Analysis Expressions) is the formula language Power BI uses to build measures, calculated columns, and calculated tables. Unlike Excel formulas, DAX evaluates against filter context, so the same formula returns different results depending on which slicers and visuals are active. This guide covers the concepts every DAX formula builds on, in the order you need them: context, CALCULATE, variables, filter functions, and the specialized topics each have their own deep-dive post.

What DAX is

DAX is a formula language for tabular data models, used in measures, calculated columns, and calculated tables. Unlike Excel, it's designed for tables that relate to each other, and formulas are evaluated inside a context that depends on where the formula sits and what's filtering the report around it. A basic measure follows this shape:

Measure Name = FUNCTION(Table[Column])

For example:

Total Sales = SUM(Sales[Revenue])

More advanced formulas combine CALCULATE, FILTER, and iterator functions like SUMX, and use variables (VAR) to keep long formulas readable and easier to debug.

Measures vs calculated columns

Measures calculate dynamically based on the filters active in a visual. Calculated columns calculate once per row, at refresh time, and don't respond to slicers.

Total Sales = SUM(Sales[Revenue])   -- measure: recalculates per filter context
Profit = Sales[Revenue] - Sales[Cost]   -- calculated column: fixed per row

Use a measure for totals, averages, KPIs, and anything that should change with user interaction. Use a calculated column for categories, flags, segmentation, and relationship keys, things that should be fixed per row. Prefer measures where either would work: calculated columns take up storage that measures don't. Our measures vs calculated columns guide covers the full comparison.

Row context vs filter context

Row context is DAX evaluating one row at a time, the default inside calculated columns and iterator functions like SUMX. Filter context is DAX evaluating a filtered subset of data, the default inside measures and visuals. CALCULATE is what converts row context into filter context, which is why so many DAX problems get fixed by adding it. Our row context vs filter context guide covers this with worked examples.

CALCULATE: the core function in DAX

CALCULATE modifies filter context, which makes it the function nearly every advanced DAX pattern is built on.

Sales in USA = CALCULATE([Total Sales], Customers[Country] = "USA")

This forces the measure to include only USA customers, regardless of what else is filtering the visual. CALCULATE can override a slicer, add a filter, remove a filter, or apply a filter conditionally, and it's the mechanism behind every time intelligence function. It also performs context transition: converting row context into filter context, which is why calculated columns and iterators need it to aggregate correctly. Our guide to the CALCULATE function covers it from first principles.

SUM vs SUMX

SUM totals a column directly:

Total Revenue = SUM(Sales[Revenue])

SUMX is an iterator: it loops row by row and evaluates an expression per row before summing:

Total Profit = SUMX(Sales, Sales[Revenue] - Sales[Cost])

Use SUM for a direct column total. Use SUMX when you need a row-level calculation, such as price times quantity, before it's summed. Our SUM vs SUMX guide compares the two in depth.

IF and SWITCH

IF works like Excel:

Sales Category = IF([Total Sales] > 100000, "High Sales", "Low Sales")

Nested IF statements get hard to read past two or three conditions. SWITCH(TRUE(), ...) handles multiple conditions more cleanly:

Sales Tier =
SWITCH(
    TRUE(),
    [Total Sales] > 1000000, "Platinum",
    [Total Sales] > 500000, "Gold",
    [Total Sales] > 100000, "Silver",
    "Bronze"
)

Our guide to writing a multiple IF statement with SWITCH covers this pattern in more depth.

DAX variables (VAR)

Variables store an intermediate calculation once instead of repeating it:

Profit Margin =
VAR Revenue = [Total Sales]
VAR Cost = [Total Cost]
RETURN
    DIVIDE(Revenue - Cost, Revenue)

Use VAR once a measure has more than one calculation step, once you're repeating the same sub-expression, or once you're combining several CALCULATE calls. It improves readability and makes measures easier to debug. Our DAX variables guide covers the full pattern.

ALL vs ALLSELECTED

ALL() removes filters entirely:

Total Sales All Regions = CALCULATE([Total Sales], ALL(Customers[Region]))

ALLSELECTED() removes the visual's own breakdown but keeps whatever the user selected in a slicer, which is why it's the usual choice for percent-of-total calculations:

Sales % of Selected =
DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED(Customers[Region])))

Our ALL vs ALLSELECTED guide covers when to use each.

Time intelligence

Time intelligence functions calculate a measure over a specific time range, such as year-to-date or the same period one year earlier, and they require a proper date table: unique dates, no gaps, marked as the date table, and related to the fact table.

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

Sales Previous Year = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DateTable[Date]))

Our time intelligence guide covers YTD, MTD, rolling totals, and year-over-year growth with the full set of functions.

Filter functions: REMOVEFILTERS, VALUES, FILTER

Beyond CALCULATE, three filter functions come up constantly:

Sales Overall = CALCULATE([Total Sales], REMOVEFILTERS(Customers))

Selected Regions = VALUES(Customers[Region])

High Value Customers = FILTER(Customers, Customers[LifetimeValue] > 50000)

REMOVEFILTERS clears filters from a table or column. VALUES returns the distinct values in the current filter context, useful inside conditional logic. FILTER builds a filtered table, most often used inside CALCULATE for conditions that a simple filter argument can't express.

Aggregation and iterator functions

The most-used aggregation functions are SUM, AVERAGE, MIN, MAX, COUNT, and DISTINCTCOUNT. Use DIVIDE instead of the / operator; it returns blank instead of an error when the denominator is zero:

Avg Order Value = DIVIDE([Total Sales], [Total Orders])

Iterator functions (SUMX, AVERAGEX, COUNTX, MINX, MAXX) evaluate an expression row by row before aggregating:

Avg Profit Per Transaction = AVERAGEX(Sales, Sales[Revenue] - Sales[Cost])

Ranking with RANKX

Customer Rank = RANKX(ALL(Customers[CustomerName]), [Total Sales], , DESC)

RANKX assigns a rank based on a measure, and recalculates as filters change. It's the function behind Top N customer or product lists. Our RANKX guide covers syntax, tie handling, and ALL vs ALLSELECTED for ranking specifically.

Text functions

DAX supports text functions including LEFT, RIGHT, MID, SEARCH, and CONTAINSSTRING:

Is Gmail = IF(CONTAINSSTRING(Customers[Email], "@gmail.com"), "Yes", "No")

Debugging DAX

Bad DAX doesn't always throw an error; it often just returns the wrong number quietly. Break a measure into VAR steps so each piece can be checked on its own, test against a small table where you can verify results by hand, and use DAX Studio to inspect what a query actually scans.

Performance practices

• Prefer measures over calculated columns where either would work.

• Avoid FILTER over large tables when a simpler filter argument works.

• Avoid iterator functions where a direct aggregation, like SUM, would do the same job.

• Avoid deeply nested CALCULATE calls; break them into VAR steps instead.

• Keep relationships in a star schema; a clean model produces simpler, faster DAX.

A fast model generally matters more than a clever formula. Our DAX performance optimization guide covers tuning measures once the model itself is sound.

Common mistakes

• Using calculated columns for things a measure would handle, which bloats model size.

• Writing a measure without understanding what context it runs in.

• Building time intelligence without a proper date table.

• Reaching for SUMX when a plain SUM would do the same job.

• Using ALL and accidentally breaking a slicer that should still apply.

• Not validating a total against the detail-level rows underneath it.

A learning order that works

1. Measure basics: SUM, COUNT, DIVIDE

2. Row context vs filter context

3. CALCULATE

4. Iterators: SUMX, AVERAGEX

5. Variables: VAR

6. Filter modifiers: ALL, REMOVEFILTERS

7. ALLSELECTED and percent-of-total patterns

8. Time intelligence

9. Ranking and other business patterns

Each step depends on the one before it: context explains why CALCULATE matters, CALCULATE explains why filter modifiers and time intelligence work the way they do.

Where this fits at CaseWhen

At CaseWhen, these are the same principles we use building DAX for client reporting models. Each linked guide above goes deeper into its own topic than this overview does.

FAQs

What is DAX in Power BI?

DAX (Data Analysis Expressions) is the formula language Power BI uses to build measures, calculated columns, and calculated tables.

Is DAX harder than Excel formulas?

DAX depends on filter context and table relationships in a way Excel formulas don't, since Excel evaluates a formula in isolation rather than against a data model.

What is CALCULATE used for?

CALCULATE changes the filter context a measure runs in. It's the function behind overriding slicers, applying conditional filters, and building every time intelligence measure.

Why does time intelligence not work sometimes?

Usually because the date table is missing, isn't marked as a date table, or isn't properly related to the fact table. See the time intelligence guide for the full checklist.

When should I use ALLSELECTED instead of ALL?

Use ALLSELECTED when a total should respect the user's slicer selection but ignore the visual's own row or column breakdown, such as a percent-of-total column in a matrix.

Sources

• DAX overview - Microsoft Learn

• Context in DAX formulas - Microsoft Learn