New: 6 free SQL practice datasets with 300+ questions — try the SQL Compiler →
Power BI

DAX Fundamentals for Power BI

DAX fundamentals explained: SUM, COUNTROWS, DIVIDE, RELATED, VAR and the X-iterators, with real results from one small Sales table.

Upskly AI Team October 5, 2026 10 min read
DAX Fundamentals for Power BI

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

Over the same six-row Sales table, the basic aggregators each answer a different question:

Eight aggregators, one shared Sales table
FunctionQuestion it answersResult
SUM(Sales[Amount])Total of every value890 (100+250+40+150+50+300)
COUNTROWS(Sales)How many rows exist6, regardless of what any column holds
COUNT(Sales[Amount])How many numeric values6, since every row has a number
COUNTA(Customers[Name])How many non-blank values of any type3, counts text too, unlike COUNT
AVERAGE(Sales[Amount])Mean value148.3333 (890 / 6), DAX keeps the decimals
DISTINCTCOUNT(Sales[CustID])How many unique values3, even though CustID repeats across orders
MAX(Sales[Amount])Largest value300
MIN(Sales[Amount])Smallest value40

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 pulls a single value across a relationship, but only in one direction: from the many side of a relationship to the one side.

RELATED only ever walks from many to one
ExpressionDirectionResult
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.

DIVIDE's zero-denominator behaviour, confirmed and reproduced
ExpressionResult
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:

Six iterator expressions, one Lines table
ExpressionResult
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:

Set functions over two small city lists
ExpressionResultWhy
COUNTROWS(UNION(CityA, CityB))4UNION appends every row and keeps duplicates, so Delhi appears twice
COUNTROWS(INTERSECT(CityA, CityB))1Only Delhi exists in both tables
COUNTROWS(EXCEPT(CityA, CityB))1Only 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

ROUND versus ROUNDUP
ExpressionResult
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:

Same three customers, two different tier logics
MeasureAshaRaviMeera
IF(SUM(Sales[Amount]) > 200, "Gold", "Silver")GoldGoldSilver
SWITCH(TRUE(), total>=400, "A", total>=200, "B", "C")ABC

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

Two different DAX objects, evaluated at two different times
MeasureCalculated column
EvaluatedAt query time, in the current filter contextOnce per row, at refresh time, using that row's own row context
Stored?No stored value at allYes, materialized like any other column
Model size impactNoneIncreases stored model size, since every row gets a value written
Can be a relationship key?NoYes

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

Quick answers
QuestionAnswer
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.

Take the Quiz
Upskly AI Team
Learning made simple
Scroll to Top