Power BI / DAX Cheat Sheet
A working reference for DAX and the Power BI model behind it — aggregations, iterators, CALCULATE and filter context, time intelligence, variables, window functions, calculation groups, Power Query (M), and the data-modeling and performance rules that decide whether a report scales. Every cheat table is searchable.
Fundamentals
DAX (Data Analysis Expressions) is the formula and query language of Power BI, Analysis Services, and Power Pivot. It is functional — every formula is a single expression that returns a value or a table — and it always evaluates inside two contexts: row context (the current row of a table) and filter context (the set of filters coming from slicers, rows/columns of a visual, and CALCULATE).
- Measure — a named formula evaluated in the current filter context at query time. Aggregates, returns a scalar, recalculates per cell. This is where most DAX lives.
- Calculated column — evaluated row-by-row at refresh and stored in the model. Has row context, no filter context. Costs memory; prefer Power Query or a measure where you can.
- Calculated table — a table materialised at refresh from a table expression (
SUMMARIZECOLUMNS,UNION,CALENDAR, …). Useful for date tables and bridge tables. - The golden rule: if a value must respond to slicers and visuals, make it a measure; if it is a fixed per-row attribute used to slice/group/relate, make it a column (ideally upstream in Power Query).
Context transition is the whole game
When a measure (or CALCULATE) is evaluated inside row context, DAX performs context transition: the current row becomes a filter. This is why SUMX(Sales, [Total Sales]) works per row, and why an unexpected total can usually be traced to a missing or extra context transition.
Measure vs calculated column
| Aspect | Measure | Calculated column |
|---|---|---|
| Evaluated | At query time, per visual cell | At data refresh, per row |
| Context | Filter context | Row context (per row) |
| Stored | No — computed on the fly | Yes — costs model memory |
| Responds to slicers | Yes | No (value is fixed at refresh) |
| Use for | Aggregations, ratios, KPIs | Row attributes to group / relate / slice |
| Typical function | SUM, CALCULATE, DIVIDE | RELATED, fixed classification |
Do not rebuild measures as columns
A calculated column cannot react to user selections — it is frozen at refresh. If you find yourself writing SUM or a ratio in a column to "make the totals work", you almost certainly want a measure. Reserve columns for attributes you slice or relate on, and push even those into Power Query when possible.
Aggregation functions
| Function | What it does | Example |
|---|---|---|
| SUM | Sum of a column | SUM(Sales[Amount]) |
| AVERAGE | Mean of a column | AVERAGE(Sales[Amount]) |
| MIN / MAX | Smallest / largest value | MAX(Sales[OrderDate]) |
| COUNT | Count non-blank numeric/date values | COUNT(Sales[OrderId]) |
| COUNTA | Count non-blank values (any type) | COUNTA(Customer[Email]) |
| COUNTROWS | Count rows of a table | COUNTROWS(Sales) |
| DISTINCTCOUNT | Count distinct values | DISTINCTCOUNT(Sales[CustomerId]) |
| COUNTBLANK | Count blanks in a column | COUNTBLANK(Customer[Phone]) |
| DIVIDE | Safe division (BLANK on /0) | DIVIDE([Profit], [Sales]) |
COUNTROWS over COUNT
To count records, prefer COUNTROWS(Table) — it counts rows regardless of column nullability, whereas COUNT(column) silently skips blanks. For unique counts use DISTINCTCOUNT, but know it is one of the more expensive aggregations on large models.
Iterators (the X functions)
Iterators evaluate an expression once per row of a table (creating row context) and then aggregate the results. Use them when the per-row calculation differs from aggregating a single stored column — for example Quantity * Price where no extended-amount column exists.
| Function | What it does | Example |
|---|---|---|
| SUMX | Sum an expression per row | SUMX(Sales, Sales[Qty] * Sales[Price]) |
| AVERAGEX | Average an expression per row | AVERAGEX(Sales, Sales[Qty] * Sales[Price]) |
| MINX / MAXX | Min / max of an expression | MAXX(Sales, Sales[Qty] * Sales[Price]) |
| COUNTX | Count non-blank results | COUNTX(Sales, Sales[Discount]) |
| PRODUCTX | Multiply results (e.g. growth) | PRODUCTX(Years, 1 + Years[Rate]) |
| RANKX | Rank each row by an expression | RANKX(ALL(Product), [Total Sales]) |
| CONCATENATEX | Join row values into a string | CONCATENATEX(VALUES(Region[Name]), Region[Name], ", ") |
Total Revenue =
SUMX (
Sales,
Sales[Quantity] * Sales[UnitPrice] * ( 1 - Sales[Discount] )
)Iterators have a cost — and a context-transition trap
An iterator scans its whole table, so iterate the smallest table that works. And referencing a measure inside an iterator triggers context transition on every row (SUMX(Customer, [Sales]) re-filters Sales per customer); that is often what you want, but it is also a common cause of slow measures.
CALCULATE & filter context
CALCULATE(expression, filter1, filter2, …) is the single most important function in DAX: it evaluates the expression in a modified filter context. Each filter argument either adds a filter (if that column was not filtered) or overwrites the existing filter on those columns — unless you wrap it in KEEPFILTERS. CALCULATE also forces context transition when used inside row context.
| Modifier | What it does | Example |
|---|---|---|
| <column> = <value> | Add/replace a filter (sugar for FILTER+ALL) | CALCULATE([Sales], Product[Color] = "Red") |
| ALL | Remove filters from a table/column(s) | CALCULATE([Sales], ALL(Product)) |
| ALLEXCEPT | Remove all filters except the listed columns | CALCULATE([Sales], ALLEXCEPT(Sales, Sales[Year])) |
| ALLSELECTED | Respect outer/slicer selection, ignore inner | CALCULATE([Sales], ALLSELECTED(Product)) |
| REMOVEFILTERS | Clear filters (modern alias of ALL) | CALCULATE([Sales], REMOVEFILTERS(Product)) |
| KEEPFILTERS | Add a filter without replacing existing | CALCULATE([Sales], KEEPFILTERS(Product[Color] = "Red")) |
| USERELATIONSHIP | Activate an inactive relationship | CALCULATE([Sales], USERELATIONSHIP(Sales[ShipDate], "Date"[Date])) |
| CROSSFILTER | Set/disable cross-filter direction | CALCULATE([Sales], CROSSFILTER(Sales[ProductId], Product[Id], BOTH)) |
% of grand total (remove a filter)
% of Total Sales =
DIVIDE (
[Total Sales],
CALCULATE ( [Total Sales], ALL ( Product ) )
)ALL vs ALLSELECTED vs ALLEXCEPT
ALL ignores every filter (true grand total). ALLSELECTED ignores filters *inside* the visual but honours outer slicers — use it for "% of the visible total". ALLEXCEPT removes everything *but* the columns you name — handy for "share within category".
A bare CALCULATE means context transition
CALCULATE([Measure]) with no filter arguments still does something inside row context: it turns the current row into a filter. In a calculated column or an iterator, that is how a measure "sees" the current row. Outside row context it is a no-op.
Filter, table & relationship functions
| Function | What it does | Example |
|---|---|---|
| FILTER | Return a row-filtered table (iterator) | FILTER(Sales, Sales[Amount] > 1000) |
| VALUES | Distinct values in context (+ blank row) | VALUES(Product[Category]) |
| DISTINCT | Distinct values, no blank row | DISTINCT(Product[Category]) |
| RELATED | Look up a value on the one-side | RELATED(Product[Category]) |
| RELATEDTABLE | Related rows on the many-side | COUNTROWS(RELATEDTABLE(Sales)) |
| CALCULATETABLE | CALCULATE for a table expression | CALCULATETABLE(Sales, Sales[Year] = 2026) |
| LOOKUPVALUE | Look up a value by key(s), no relationship | LOOKUPVALUE(Rate[Pct], Rate[Tier], [Tier]) |
| TREATAS | Apply a table as a filter on columns | TREATAS(VALUES(T[Id]), Sales[CustomerId]) |
| SUMMARIZE / SUMMARIZECOLUMNS | Group + aggregate into a table | SUMMARIZECOLUMNS(Product[Category], "Sales", [Total Sales]) |
| SELECTCOLUMNS / ADDCOLUMNS | Project / extend a table | ADDCOLUMNS(VALUES(Product[Id]), "S", [Total Sales]) |
| EXCEPT / INTERSECT / UNION | Set operations on tables | EXCEPT(VALUES(A[Id]), VALUES(B[Id])) |
RELATED vs RELATEDTABLE
Follow the relationship "uphill" from many to one with RELATED (a scalar from the lookup table). Go "downhill" from one to many with RELATEDTABLE (a table you then aggregate). Both need an active relationship; LOOKUPVALUE works without one but is slower.
FILTER over a whole table is a smell
Prefer the simple predicate form — CALCULATE([Sales], Product[Color] = "Red") — over CALCULATE([Sales], FILTER(Product, Product[Color] = "Red")). Reach for explicit FILTER only when the condition references a measure or needs the full row context; wrapping it around a giant fact table needlessly is a top performance mistake.
Logical & information functions
| Function | What it does | Example |
|---|---|---|
| IF | Branch on a condition | IF([Sales] > 0, "Yes", "No") |
| SWITCH | Match a value to results | SWITCH([Grade], 1, "A", 2, "B", "Other") |
| SWITCH(TRUE()) | If/elseif ladder | SWITCH(TRUE(), [S] > 1000, "Hi", "Lo") |
| AND / OR / NOT | Boolean composition (or && / ||) | AND([A] > 0, [B] > 0) |
| COALESCE | First non-blank value | COALESCE([Sales], 0) |
| IFERROR | Catch and replace errors | IFERROR([A] / [B], 0) |
| ISBLANK / ISERROR | Test for blank / error | IF(ISBLANK([Sales]), 0, [Sales]) |
| HASONEVALUE | Is exactly one value in context? | IF(HASONEVALUE(Product[Color]), …) |
| SELECTEDVALUE | The single value in context, else default | SELECTEDVALUE(Product[Color], "All") |
| ISFILTERED / ISINSCOPE | Is a column filtered / a grouping level? | ISINSCOPE(Product[Category]) |
| DIVIDE | Safe division | DIVIDE([Profit], [Sales], 0) |
SWITCH(TRUE()) beats nested IFs
For multi-branch logic, SWITCH(TRUE(), cond1, val1, cond2, val2, default) is far more readable than nesting IFs, and it short-circuits at the first true condition.
Text & FORMAT
| Function | What it does | Example |
|---|---|---|
| & / CONCATENATE | Join two strings | [FirstName] & " " & [LastName] |
| CONCATENATEX | Join a column with a delimiter | CONCATENATEX(VALUES(T[Name]), T[Name], ", ") |
| FORMAT | Value to formatted text | FORMAT([Sales], "$#,0.00") |
| LEFT / RIGHT / MID | Substring by position | LEFT([Code], 3) |
| LEN | String length | LEN([Name]) |
| UPPER / LOWER | Change case | UPPER([Code]) |
| TRIM | Strip extra spaces | TRIM([Name]) |
| SUBSTITUTE | Replace text by match | SUBSTITUTE([Phone], "-", "") |
| REPLACE | Replace text by position | REPLACE([Code], 1, 2, "XX") |
| SEARCH / FIND | Position of a substring (in / case-sen) | SEARCH("@", [Email]) |
| VALUE | Text to number | VALUE([TextNumber]) |
| COMBINEVALUES | Join keys for a relationship | COMBINEVALUES("|", [A], [B]) |
FORMAT returns text — and kills sorting
A measure wrapped in FORMAT becomes a string, so visuals sort it alphabetically and lose numeric ordering. Format with the measure formatting properties (or a dynamic format string / calculation group) instead of FORMAT whenever the result still needs to behave like a number.
Time intelligence
Time intelligence shifts and accumulates over dates. It requires a dedicated Date table with a contiguous, day-grain date column, related to your fact table and flagged with Mark as date table. Never run time intelligence off a date column in the fact table itself.
| Function | What it does | Example |
|---|---|---|
| TOTALYTD | Year-to-date running total | TOTALYTD([Sales], "Date"[Date]) |
| TOTALQTD / TOTALMTD | Quarter / month to date | TOTALMTD([Sales], "Date"[Date]) |
| DATESYTD | YTD date set (use inside CALCULATE) | CALCULATE([Sales], DATESYTD("Date"[Date])) |
| SAMEPERIODLASTYEAR | Same dates, one year back | CALCULATE([Sales], SAMEPERIODLASTYEAR("Date"[Date])) |
| DATEADD | Shift dates by an interval | CALCULATE([Sales], DATEADD("Date"[Date], -1, MONTH)) |
| PARALLELPERIOD | Shift to a full parallel period | CALCULATE([Sales], PARALLELPERIOD("Date"[Date], -1, YEAR)) |
| PREVIOUSMONTH / PREVIOUSYEAR | The prior whole period | CALCULATE([Sales], PREVIOUSMONTH("Date"[Date])) |
| DATESINPERIOD | A rolling window of N intervals | CALCULATE([Sales], DATESINPERIOD("Date"[Date], MAX("Date"[Date]), -3, MONTH)) |
| DATESBETWEEN | Explicit date range | CALCULATE([Sales], DATESBETWEEN("Date"[Date], d1, d2)) |
| ENDOFMONTH / STARTOFYEAR | Boundary dates of a period | ENDOFMONTH("Date"[Date]) |
Year-over-year and YoY %
Sales PY =
CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
Sales YoY % =
VAR Curr = [Total Sales]
VAR Prior = [Sales PY]
RETURN
DIVIDE ( Curr - Prior, Prior )A broken date table breaks everything
Classic time intelligence errors out if the date column has gaps between its first and last date, or if it is not marked as a date table. Generate the table with CALENDAR/CALENDARAUTO (or in Power Query), cover full years, keep it at day grain, and relate it on the date — not on a separate integer key — unless you are deliberately using a date-key model.
Variables (VAR / RETURN)
Variables make measures faster, more readable, and easier to debug. A VAR is evaluated once, in the filter context where it is declared, and its value is then constant wherever it is referenced — which also makes it a precise debugging tool.
Sales YoY Growth % =
VAR SalesPriorYear =
CALCULATE ( [Total Sales], PARALLELPERIOD ( 'Date'[Date], -12, MONTH ) )
RETURN
DIVIDE ( [Total Sales] - SalesPriorYear, SalesPriorYear )Variables capture context where they are declared
A variable is not re-evaluated when used inside a later CALCULATE that changes the filter context — it keeps the value it had at declaration. That is usually a feature (predictable, single evaluation), but it surprises people who expect a variable to "follow" a context change. Declare it inside the CALCULATE if you need the modified context.
Debug by returning the variable
Temporarily RETURN an intermediate variable to see exactly what it holds. Combined with DAX query view, this is the fastest way to dissect a measure that returns the wrong number.
Measure cookbook
Battle-tested measure patterns — paste into a new measure and swap the table/column names for your model.
Running / cumulative total
Running Total =
CALCULATE (
[Total Sales],
FILTER (
ALLSELECTED ( 'Date'[Date] ),
'Date'[Date] <= MAX ( 'Date'[Date] )
)
)% of category (share within parent)
% of Category =
DIVIDE (
[Total Sales],
CALCULATE ( [Total Sales], ALLEXCEPT ( Product, Product[Category] ) )
)Rank products (ties skipped, descending)
Product Rank =
RANKX ( ALL ( Product[Name] ), [Total Sales], , DESC, SKIP )Rolling 3-month average
Rolling 3M Avg =
AVERAGEX (
DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, MONTH ),
[Total Sales]
)Distinct customers who bought (active customers)
Active Customers =
CALCULATE ( DISTINCTCOUNT ( Sales[CustomerId] ), Sales[Amount] > 0 )New customers (first order in the current period)
New Customers =
VAR PeriodStart = MIN ( 'Date'[Date] )
RETURN
COUNTROWS (
FILTER (
VALUES ( Sales[CustomerId] ),
CALCULATE ( MIN ( Sales[OrderDate] ), ALLEXCEPT ( Sales, Sales[CustomerId] ) )
>= PeriodStart
)
)Dynamic title / subtitle measure
Report Title =
"Sales for " &
SELECTEDVALUE ( 'Date'[Year], "All Years" ) & " — " &
SELECTEDVALUE ( Region[Name], "All Regions" )Harvest a slicer value (what-if / parameter)
Adjusted Forecast =
[Total Sales] * ( 1 + SELECTEDVALUE ( 'Growth %'[Growth %], 0 ) )Blank out a measure outside its valid range
Sales (no future) =
IF ( MAX ( 'Date'[Date] ) <= TODAY (), [Total Sales] )Window functions
The DAX window functions (2022+) calculate over a set of rows relative to the current row, similar to SQL OVER (PARTITION BY … ORDER BY …). They pair with the helper clauses ORDERBY, PARTITIONBY, and MATCHBY, and shine for previous-row, ranking, and moving-window calculations without hand-rolled FILTER logic.
| Function | What it does | Returns |
|---|---|---|
| OFFSET | Row before/after the current row by N | A single row |
| INDEX | Row at an absolute position | A single row |
| WINDOW | A range of rows (from/to bounds) | Multiple rows |
| RANK | Ranking within the partition (ties share) | An integer |
| ROWNUMBER | Unique sequential number (breaks ties) | An integer |
| ORDERBY | Defines sort within each partition | (helper clause) |
| PARTITIONBY | Defines how the relation is partitioned | (helper clause) |
Sales vs Prev Month =
VAR Prev =
CALCULATE (
[Total Sales],
OFFSET ( -1, ALLSELECTED ( 'Date'[Year], 'Date'[Month] ),
ORDERBY ( 'Date'[Year], ASC, 'Date'[Month], ASC ) )
)
RETURN
[Total Sales] - PrevWindow functions are not just for visuals
Unlike the visual calculations introduced alongside them, OFFSET/INDEX/WINDOW/RANK/ROWNUMBER work in ordinary measures over a table expression. They are evaluated in the current filter context, so the partition you pass (often ALLSELECTED) defines the "window" the user sees.
Calculation groups, field parameters & UDFs
When you find yourself writing the same pattern across many measures (PY, YTD, YoY for every KPI), stop copying DAX and reuse it at the model level.
Calculation group — one time-intelligence pattern for all measures
-- Calculation item: "YTD"
CALCULATE ( SELECTEDMEASURE (), DATESYTD ( 'Date'[Date] ) )
-- Calculation item: "PY"
CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
-- Calculation item: "YoY %"
DIVIDE (
SELECTEDMEASURE () - CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) ),
CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
)- Calculation groups turn N measures × M variations into one reusable set of *calculation items*. Built in Tabular Editor or the model view;
SELECTEDMEASURE(),SELECTEDMEASURENAME(), and precedence control how multiple groups combine. - Field parameters let users swap which measure or dimension a visual shows from a slicer — great for "choose your metric" report pages, no extra DAX per option.
- DAX user-defined functions (UDFs) package a parameterised expression you can call by name across measures — define them in DAX query view or TMDL with
FUNCTION name = (param) => …. - Dynamic format strings format a measure per calculation item or per value (e.g. show K/M/B), so numbers stay numeric while displaying cleanly.
Calculation groups make measures variant-typed
Adding any calculation group switches all model measures to the variant data type, which can surface "Cannot convert Text to Numeric" errors on non-numeric measures (dynamic titles) and break some dynamic format strings. Guard calculation items with ISNUMERIC ( SELECTEDMEASURE () ) where a measure might be text.
Power Query (M) essentials
Power Query (the M language) is where data is loaded, cleaned, and shaped *before* it reaches the model. The rule of thumb: shape in Power Query, calculate in DAX. Do row-level cleanup, type-setting, and column creation here, not in calculated columns.
| Transform | What it does | M function |
|---|---|---|
| Filter rows | Keep rows matching a condition | Table.SelectRows |
| Remove columns | Drop columns | Table.RemoveColumns |
| Rename columns | Rename columns | Table.RenameColumns |
| Change type | Set data types (do this early) | Table.TransformColumnTypes |
| Add custom column | Compute a new column | Table.AddColumn |
| Group by | Aggregate to a coarser grain | Table.Group |
| Merge queries | Join two queries (lookup) | Table.NestedJoin |
| Append queries | Stack rows (union) | Table.Combine |
| Unpivot | Columns → attribute/value rows | Table.UnpivotOtherColumns |
| Split column | Split by delimiter/position | Table.SplitColumn |
| Replace values | Find and replace | Table.ReplaceValue |
Protect query folding
Against a database, Power Query tries to fold your steps into a single SQL query at the source — far faster than pulling everything and transforming locally. Keep folding alive by doing filtering/typing early and avoiding folding-breakers (custom M that the source cannot translate, Table.Buffer, some index/merge patterns). Right-click a step → View Native Query to confirm it still folds.
Reference, do not duplicate; parameterise sources
Use Reference to branch a query from a common base (one refresh of the base), not Duplicate (which copies all steps). Put server/database names and environment values in parameters so the same report promotes cleanly across dev/test/prod.
Data modeling best practices
DAX is only as good as the model under it. A clean star schema — narrow fact tables surrounded by dimension tables — is what makes measures simple, fast, and correct.
- Star schema, not snowflake or flat. One fact table per business process, dimensions around it. Avoid one giant wide table — it bloats memory and complicates filtering.
- Single-direction relationships by default. Bidirectional cross-filtering is convenient but causes ambiguity, performance hits, and surprising totals; enable it only deliberately (e.g. many-to-many bridges) and prefer
CROSSFILTER/TREATASin DAX instead. - A dedicated Date table, marked as such, related on a date (or date key). Hide the fact table’s raw date once the relationship exists.
- Hide keys and technical columns from report view, and set data categories (e.g. geography) and summarisation (set IDs to "Don’t summarize").
- Integer surrogate keys for relationships outperform text/GUID keys; reduce cardinality wherever you can — high-cardinality columns dominate the model size.
- Prefer measures over calculated columns, and calculated columns over big imported flags; push static row logic into Power Query or the source.
- Disable Auto date/time (Options → Data Load) — it silently creates a hidden date table per date column and bloats the file.
Measure your model with the tools built for it
Use DAX Studio (VertiPaq Analyzer) to find the columns eating memory and the slow queries, and Tabular Editor to script measures, calculation groups, and best-practice rules. Both are standard kit for production Power BI work.
DAX queries & testing
DAX is also a query language. In Power BI Desktop’s DAX query view (or the service’s "Write DAX queries"), EVALUATE returns a table and DEFINE MEASURE lets you prototype or edit measures without touching the model — then push them in with CodeLens.
DEFINE
MEASURE Sales[Test Margin %] =
DIVIDE ( [Total Profit], [Total Sales] )
EVALUATE
SUMMARIZECOLUMNS (
Product[Category],
"Sales", [Total Sales],
"Margin %", [Test Margin %]
)
ORDER BY [Sales] DESCEVALUATE { } to inspect a scalar
Wrap a scalar expression in a table constructor to run it as a query: EVALUATE { [Total Sales] }. Use Define and evaluate from the measure’s context menu to dump an existing measure (and its referenced measures) into an editable query for debugging.
Performance & gotchas
- Use `DIVIDE`, not `/` when the denominator can be zero or blank — it avoids errors and returns BLANK (which drops empty groupings from visuals). Use
/only for constant denominators. - Avoid `FILTER` over a whole fact table — use the simple
CALCULATEpredicate form, or filter the smallest column/dimension that achieves the result. - Lift repeated sub-expressions into `VAR`s — they evaluate once and make measures both faster and readable.
- Watch context transition in iterators —
SUMX(BigTable, [Measure])re-filters per row and can be very slow; iterate a dimension or pre-aggregate where possible. - Bidirectional and many-to-many relationships are costly and can produce ambiguous results; reach for them sparingly.
- `DISTINCTCOUNT` and high-cardinality columns are expensive — reduce cardinality, and consider approximate counts only when exactness is not required.
- Return `BLANK()` rather than 0 for "no data" so visuals stay clean — but be deliberate when 0 is a meaningful value.
- Measure totals are not the sum of rows. A measure is re-evaluated at the total level in its own filter context, so a ratio/average total is correct, not additive. If a total looks "wrong", that is usually the cause.
- Don’t format with `FORMAT` when sorting/aggregation matters — use measure format strings or dynamic format strings to keep the value numeric.
Tip
Every cheat table above is searchable
Use the filter box on each table to jump to a function fast. This whole guide is generated from one structured dataset, so it stays in sync with the FlowLibs MCP reference.