DAX, Data Analysis Expressions, is the formula language behind every Power BI measure and calculated column. Below, every result is run for real against the Excel Data Model, the same VertiPaq engine Power BI itself uses, over one small Sales table: six orders, amounts 100, 250, 40, 150, 50 and 300, for three customers, Asha, Ravi and Meera.
In this guide
- The basic aggregators
- RELATED, and which direction it can go
- DIVIDE, and why it beats the / operator
- VAR: naming a sub-expression once
- SUMX, AVERAGEX and the other X-iterators
- UNION, INTERSECT and EXCEPT
- ROUND, ROUNDUP, and why rounding is not formatting
- IF, SWITCH(TRUE()), and implicit aggregation
- Measure versus calculated column
- BLANK, and COALESCE
- Common mistakes
- Cheat sheet
- Try it yourself
- FAQ
The basic aggregators
Over the same six-row Sales table, the basic aggregators each answer a different question:
| Function | Question it answers | Result |
|---|---|---|
| SUM(Sales[Amount]) | Total of every value | 890 (100+250+40+150+50+300) |
| COUNTROWS(Sales) | How many rows exist | 6, regardless of what any column holds |
| COUNT(Sales[Amount]) | How many numeric values | 6, since every row has a number |
| COUNTA(Customers[Name]) | How many non-blank values of any type | 3, counts text too, unlike COUNT |
| AVERAGE(Sales[Amount]) | Mean value | 148.3333 (890 / 6), DAX keeps the decimals |
| DISTINCTCOUNT(Sales[CustID]) | How many unique values | 3, even though CustID repeats across orders |
| MAX(Sales[Amount]) | Largest value | 300 |
| MIN(Sales[Amount]) | Smallest value | 40 |
COUNT and COUNTA are not interchangeable. COUNT only counts numeric values in a column; COUNTA counts any non-blank value, including text, which is why COUNTA is the one that works on a Name column at all.
RELATED, and which direction it can go
RELATED pulls a single value across a relationship, but only in one direction: from the many side of a relationship to the one side.
| Expression | Direction | Result |
|---|---|---|
| ADDCOLUMNS(Sales, "CustName", RELATED(Customers[Name])) | Sales (many) to Customers (one) | Asha for OrderID 3, since that order's CustID is 1 |
| ADDCOLUMNS(Sales, "CustCity", RELATED(Customers[City])) | Sales (many) to Customers (one) | Pune for OrderID 4, since that order's CustID is 3, Meera |
| Customers[Total] = RELATED(Sales[Amount]) | Customers (one) to Sales (many) | Fails to compile: RELATED cannot reach from the one side back to the many side |
The failing case matters: a customer can have several orders, so there is no single Sales value RELATED could sensibly return for one Customers row. Pulling a value the other way, from the one side down to the many side’s rows, needs SUMX or RELATEDTABLE instead, aggregating across the several matching rows rather than fetching a single one.
DIVIDE, and why it beats the / operator
DIVIDE(numerator, denominator, [alternate]) behaves exactly like the / operator with a non-zero denominator, but handles zero safely instead of erroring.
| Expression | Result |
|---|---|
| DIVIDE(300, 2) | 150, an ordinary division |
| DIVIDE(10, 0) | BLANK, with no third argument supplied |
| DIVIDE(10, 0, -1) | -1, the third argument is the alternate result used only when dividing by zero |
| ISBLANK(DIVIDE(10, 0)) | TRUE, confirming the BLANK above |
| IF(ISBLANK(DIVIDE(10, 0)), -1, DIVIDE(10, 0)) | -1, the same safe fallback written out by hand |
A plain a / b throws an error the moment b is 0 for even a single row, which can break an entire visual. DIVIDE is built specifically to avoid that, returning BLANK, or a chosen alternate result, instead.
VAR: naming a sub-expression once
VAR computes an expression once and reuses the stored result under a name, which is both clearer to read and can be faster than repeating the same sub-expression several times inside one measure.
VAR total = SUM(Sales[Amount])
VAR disc = total * 0.1
RETURN total - disc
Over the Sales table, total is 890, disc is 89, and the measure returns 890 – 89 = 801. A second measure, VAR avgAmt = AVERAGE(Sales[Amount]) VAR biggest = MAX(Sales[Amount]) RETURN biggest - avgAmt, returns 300 – 148.3333 = 151.6667, and a third, comparing total / n against 150, returns “At or Below”, since 890 / 6 is 148.3333, which does not clear 150.
SUMX, AVERAGEX and the other X-iterators
Every X-suffixed function, SUMX, AVERAGEX, MAXX, MINX, COUNTX, evaluates its row expression once per row of the given table, and only then aggregates the per-row results. Over a Lines table (Pen 10×5, Book 4×20, Pen 6×5, Pencil 20×2), the four line totals, Qty times UnitPrice, are 50, 80, 30 and 40:
| Expression | Result |
|---|---|
| SUMX(Lines, Lines[Qty] * Lines[UnitPrice]) | 200, the four line totals added together |
| AVERAGEX(Lines, Lines[Qty] * Lines[UnitPrice]) | 50, the same 200 divided by 4 rows |
| MAXX(Lines, Lines[Qty] * Lines[UnitPrice]) | 80, the Book line |
| MINX(Lines, Lines[Qty] * Lines[UnitPrice]) | 30, the second Pen line |
| COUNTX(Lines, IF(Lines[Qty] > 5, Lines[Qty])) | 3, the IF returns BLANK only for the Book row (Qty 4) |
| SUMX(FILTER(Lines, Lines[Product] = "Pen"), Lines[Qty] * Lines[UnitPrice]) | 80, only the two Pen rows, 50 + 30 |
UNION, INTERSECT and EXCEPT
With CityA holding Mumbai and Delhi, and CityB holding Delhi and Pune:
| Expression | Result | Why |
|---|---|---|
| COUNTROWS(UNION(CityA, CityB)) | 4 | UNION appends every row and keeps duplicates, so Delhi appears twice |
| COUNTROWS(INTERSECT(CityA, CityB)) | 1 | Only Delhi exists in both tables |
| COUNTROWS(EXCEPT(CityA, CityB)) | 1 | Only Mumbai is in CityA but missing from CityB |
DAX’s UNION behaves like SQL’s UNION ALL, not plain UNION: duplicates are kept, not removed. Wrap the result in DISTINCT to drop them. All three functions also match columns by position, not by name, so the tables just need the same number of columns, not identical names or identical row counts.
ROUND, ROUNDUP, and why rounding is not formatting
| Expression | Result |
|---|---|
| ROUND(148.3333, 1) | 148.3, normal rounding rules |
| ROUNDUP(148.1, 0) | 149, always rounds away from zero, never down, even for a small fraction |
Rounding changes the stored value; formatting does not
If 148.3333 is only given a display format of one decimal, it still shows 148.3, but any later formula using that value calculates from the full 148.3333. ROUND(148.3333, 1) genuinely changes the value to 148.3, so a later formula built on the ROUND result calculates from 148.3 instead. The two look identical on screen and are not the same thing.
IF, SWITCH(TRUE()), and implicit aggregation
Per-customer totals are Asha 440 (100+40+300), Ravi 300 (250+50) and Meera 150. Two different branching measures over those totals:
| Measure | Asha | Ravi | Meera |
|---|---|---|---|
| IF(SUM(Sales[Amount]) > 200, "Gold", "Silver") | Gold | Gold | Silver |
| SWITCH(TRUE(), total>=400, "A", total>=200, "B", "C") | A | B | C |
SWITCH(TRUE(), cond1, result1, cond2, result2, default) still checks conditions top to bottom exactly like nested IFs would, but lays them out as a flat, ordered list instead of nesting IFs inside each other, which stays readable as more branches are added.
Separately, dragging a plain numeric column straight onto a visual, with no measure written at all, does not show raw unaggregated values: Power BI wraps it in an implicit aggregation, commonly SUM, automatically.
Measure versus calculated column
| Measure | Calculated column | |
|---|---|---|
| Evaluated | At query time, in the current filter context | Once per row, at refresh time, using that row's own row context |
| Stored? | No stored value at all | Yes, materialized like any other column |
| Model size impact | None | Increases stored model size, since every row gets a value written |
| Can be a relationship key? | No | Yes |
A measure has no stored value of its own; it is recalculated every single time it is used, in whatever filter context the report or query applies at that moment. A calculated column’s formula instead runs once per row while the data refreshes, with that row’s own column values available, and the result is then stored exactly like any other column, which is also why only a calculated column, never a measure, can serve as a relationship’s own join key.
BLANK, and COALESCE
BLANK() represents a missing or empty value, close to NULL in SQL, and DAX treats it differently from both the number 0 and an empty text string in arithmetic and comparisons. COALESCE(a, b, c) checks its arguments left to right and returns the first one that is not BLANK, falling back to a later argument only when an earlier one is blank.
Common mistakes
- Using RELATED from the one side. It only walks from many to one; the reverse needs SUMX or RELATEDTABLE.
- Writing a / b instead of DIVIDE. A single zero denominator in any row can break the whole visual; DIVIDE handles it safely.
- Treating BLANK as 0. They are different, and arithmetic and comparisons can behave differently around each.
- Assuming UNION removes duplicates. It does not; wrap it in DISTINCT if duplicates need to go.
- Confusing a rounded value with a formatted one. Only ROUND changes what a later formula actually calculates from.
- Expecting a calculated column to update live. It is fixed at refresh time, unlike a measure.
Cheat sheet
| Question | Answer |
|---|---|
| What counts physical rows? | COUNTROWS |
| What counts numeric values only? | COUNT |
| What counts any non-blank value, text included? | COUNTA |
| Which direction can RELATED go? | Many side to one side, only |
| What is DIVIDE's third argument for? | The result to use when dividing by zero |
| What do the X-iterators do? | Evaluate per row, then aggregate the row results |
| Does DAX's UNION remove duplicates? | No, like SQL's UNION ALL |
| What changes a stored value, not just its display? | ROUND, not number formatting |
| Which one can be a relationship's join key? | A calculated column, never a measure |
| What represents a missing value in DAX? | BLANK |
Try it yourself
Quick check
Over the same Lines table (Pen 10×5, Book 4×20, Pen 6×5, Pencil 20×2), what does AVERAGEX(FILTER(Lines, Lines[Qty] > 5), Lines[Qty] * Lines[UnitPrice]) return?
Show answer
40. FILTER keeps the three rows with Qty over 5: the first Pen (Qty 10, line total 50), the second Pen (Qty 6, line total 30) and the Pencil (Qty 20, line total 40); the Book row is excluded since its Qty is only 4. AVERAGEX then averages those three surviving line totals, 50, 30 and 40, which sum to 120 and divide by 3 rows to give 40.
Frequently asked questions
What is DAX in Power BI?
DAX, Data Analysis Expressions, is the formula language used for every measure and calculated column in a Power BI model, covering aggregation, filtering, row-by-row iteration and time intelligence.
What is the difference between a measure and a calculated column in DAX?
A measure has no stored value and is recalculated at query time in the current filter context. A calculated column is computed once per row at refresh time and stored like any other column, which is also why it increases the model’s stored size and a measure does not.
Why use DIVIDE instead of the / operator in DAX?
A plain a / b throws an error the moment b is 0 for any row, which can break a visual entirely. DIVIDE is built to handle a zero denominator safely, returning BLANK or a chosen alternate result instead.
What do SUMX, AVERAGEX and the other X-functions do differently from SUM and AVERAGE?
The X-iterators evaluate a row-level expression once for every row of a given table first, then aggregate those per-row results, which lets them compute something like quantity times price per line before summing, rather than aggregating a single existing column directly.
Does DAX’s UNION function remove duplicate rows?
No. DAX’s UNION behaves like SQL’s UNION ALL, keeping every row including duplicates; DISTINCT needs to be applied on top if duplicates should be removed.
What is BLANK in DAX?
BLANK represents a missing or empty value, similar to NULL in SQL, and DAX treats it differently from both the number 0 and an empty text string in arithmetic and comparisons.
Test yourself
Timed questions on DAX Fundamentals, with an explanation for every answer.