DAX Fundamentals
The language behind every Power BI measure
DAX is the formula language of Power BI and Analysis Services: measures computed at query time, table expressions, you call them on a calendar.
What you will learn
- Read and write basic DAX measures and syntax
- Distinguish measures from calculated columns
- Recognize the two evaluation contexts at work
What is this?
DAX (Data Analysis eXpressions) is the formula language of Power BI, Power Pivot and Analysis Services tabular. A measure is a DAX expression evaluated at query time against the current filter context — the slices the user has selected. Under the hood, DAX works on whole tables (column-level filters), not cell by cell, which is why it can both look like Excel formulas and behave like a set language.
Why do you need it?
- Measures in DAX react to slicers and filters automatically.
- The language is a few dozen core functions — reusable across reports.
- Because it evaluates on columns and tables, results scale to millions of rows.
- Learning the context model unlocks the entire Power BI calculation layer.
Mental model
Excel formulas evaluated per cell
DAX measures evaluated per filter state
In Excel you write per-cell math that copies down. In DAX you write per-model math that re-computes for whatever subset of rows the filters select — same idea, whole different engine, evaluated against the whole columnar model instead of one cell.
How it works
- A measure is compiled into a query that evaluates against the model's columnar storage.
- The current filter context (slicers, page, visual grouping) restricts which rows each column refers to.
- Scalar functions like SUM aggregate over those rows at query time.
- Iterator functions like SUMX add an explicit row-by-row pass before aggregating.
- CALCULATE modifies the context itself, making it the master tool of DAX.
Measure evaluation from visual to columnar answer.
Examples
Your first two measures
Revenue = SUM(fact_orders[amount])Orders = COUNTROWS(fact_orders)AOV = DIVIDE([Revenue], [Orders])SUM adds the amount column's filtered rows; COUNTROWS counts them; AOV divides, protecting against division by zero with DIVIDE.
In production
- A sales model defines [Revenue], [Orders] and [AOV] once — every visual references the same measures.
- A churn measure is ported from an Excel formula to DAX so leadership and ops see identical numbers.
- A finance team replaces a report of hard-coded totals with measures that auto-rescale to the selected quarter.
Best practices
- Name measures like nouns with a leading participle ([Total Revenue], [Orders Shipped]).
- Put measures in the model, not typed inside visuals.
- Prefer DIVIDE over '/' for division to handle zero gracefully.
- Test a measure against a known source total before wiring it into a dashboard.
Common mistakes
- Re-typing the same formula in five visuals instead of one model measure.
- Expecting a measure to behave like an Excel cell that changes per row.
- Forgetting PARENTHESES and commas — DAX is strict about separator locale.
- Using calculated columns for everything because they 'just work' in tables.
Debugging tips
- Blank result → check DIVIDE by zero, empty filtered set, or a typo'd column.
- Wrong total → inspect the filter context, not the formula.
- Slow measure → open DAX Studio and profile the generated query plan.
Interview checkpoint
Hands-on lab
- Translate DAX aggregations into SQL that mimics engine behavior
- Compare SUM vs a per-row iterator pass
- Verify results against a known source total
Model a Measure with SQLite Rows
45 min · intermediate
Quick quiz
Which DAX function safely handles division by zero?
A measure re-computes whenever the user changes a slicer.