DAX Formulas Guide for Excel Power Pivot
DAX (Data Analysis Expressions) is Power Pivot's formula language — Excel-like at first glance, but built for tables and filter context instead of cells. This guide covers the essential DAX functions and patterns you'll use in almost every Power Pivot model.
How DAX differs from Excel formulas
- No cell references. DAX works over tables and columns. There is no A1 or B2.
- Column references use table syntax.
Sales[Amount]— table name in front, column in brackets. - Filter context matters. The same measure returns different values in different PivotTable positions. This is the point.
- Iterator functions exist.
SUMX,AVERAGEX,MAXXloop over rows to compute row-level values before aggregating.
Aggregation functions
Iterators — the X functions
Regular SUM aggregates a single column. Iterators like SUMX loop over rows, compute a value per row, then aggregate.
Iterators: SUMX, AVERAGEX, COUNTX, MAXX, MINX, RANKX
If you're summing an existing column: SUM(Sales[Amount]). If you need to compute per-row math before summing: SUMX(Sales, Sales[Qty] * Sales[Price]). Never wrap SUM in SUMX — it doesn't do what you think.
Filter functions
CALCULATE — the central function
CALCULATE evaluates its first argument (an expression) under a modified filter context (subsequent arguments). It's the mechanism behind most non-trivial DAX.
FILTER — build a filtered table
ALL — remove filters
ALLEXCEPT — remove all filters except specified
Relationship functions
RELATED — pull a column from the "one" side
Used in calculated columns on the "many" side of a one-to-many relationship.
RELATEDTABLE — pull rows from the "many" side
Used in calculated columns on the "one" side to count related rows on the "many" side.
Time intelligence
These functions require a proper Calendar table marked as Date Table.
Logical functions
SWITCH with TRUE() is cleaner than nested IFs for multi-branch logic.
Text functions
Number and math
DIVIDE returns 0 (or a specified alternative) on divide-by-zero. Much safer than /.
Date functions
Common patterns
Percentage of parent category
Ranking within a group
Running total by date
Top N filter
New customers this month
Variables
Use VAR and RETURN to break complex expressions into named parts:
Variables improve readability, prevent recalculation (each VAR is computed once), and make debugging easier.
Common pitfalls
SUMX(Sales, SUM(Sales[Amount])) is a common mistake. SUMX iterates row-by-row; wrapping SUM inside creates a Cartesian effect. If you need row-level math, use SUMX(Sales, Sales[Qty] * Sales[Price]).
Using / can produce infinity or errors on zero denominators. Always prefer DIVIDE() in DAX — it handles the zero case cleanly.
SAMEPERIODLASTYEAR, DATESYTD, and similar require a Calendar with contiguous dates and Date Table marking. Without one, they return blank or wrong values silently.
Nested IFs get unreadable fast. SWITCH(TRUE(), condition1, result1, condition2, result2, default) is far cleaner.