Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 56 ยท DAX

DAX Basics: Syntax, Columns and Measures

Learn what DAX is, how its syntax works, and when to use a calculated column, a measure or a calculated table, with a pandas stand-in you can run.

DAX (Data Analysis Expressions) is the formula language of Power BI. Excel users will find it familiar, because it uses function calls such as SUM and IF, but there is one big difference: DAX works on columns and tables, not on cells, and the same formula gives different answers depending on where it is shown. Understanding that idea early makes everything else in this chapter easier.

In this lesson you learn the syntax, the three places where DAX lives (calculated columns, measures and calculated tables) and how to choose between them. The model we build on comes from the previous chapter: see Star Schema and Relationships.

DAX runs inside Power BI Desktop, Analysis Services and Fabric, so it cannot run on this site. DAX code below is read-only. The runnable examples are Python (pandas) stand-ins that reproduce what a formula calculates, and each section says honestly which part is real and which is the stand-in.

Where DAX code lives

Three homes for DAX

Calculated column One value per row, stored in the model.
Measure Calculated on demand for the visual.
Calculated table A whole new table built from a formula.

Measures are the ones that react to slicers and filters.

1 / 2

You create each one on the Table tools or Modeling ribbon: New column, New measure and New table, then type the formula in the formula bar.

The syntax of a formula

A DAX definition always has the same shape: a name, an equals sign, an expression.

DAX
Total Sales = SUM(Sales[Amount])

The parts:

  • Total Sales is the name. Measures and tables can contain spaces. A measure name is referenced in square brackets alone: [Total Sales].
  • SUM is a function, and its argument is a column reference: the table name, then the column in square brackets, Sales[Amount].
  • Strings use double quotes ("North"), and DAX is not case sensitive for function names, though everyone types them in upper case.

Quote a table name with single quotes when it has spaces or special characters: 'Sales Orders'[Amount]. A good habit is to always write the table name before a column (Sales[Amount]) and never before a measure ([Total Sales]), so you can tell them apart at a glance.

Operators

Kind Operators Example
Arithmetic + - * / ^ [Revenue] - [Cost]
Comparison = <> < <= > >= Sales[Quantity] > 10
Text concatenation & Customer[First] & " " & Customer[Last]
Logical &&, ||, NOT, IN Sales[Region] IN {"North", "South"}

Equality uses a single =, not ==. Use DIVIDE(a, b) instead of a / b when the denominator might be zero, as covered in the next lesson.

Variables

VAR and RETURN let you name an intermediate result. They make long formulas readable and avoid recomputing the same value:

DAX
Margin % =
VAR Revenue = SUM(Sales[Amount])
VAR Cost = SUM(Sales[Cost])
RETURN
    DIVIDE(Revenue - Cost, Revenue)

Comments start with // or --, or sit between /* and */. Press Shift+Enter to start a new line in the formula bar.

Calculated columns

A calculated column is evaluated once for every row of its table, when the data is loaded or refreshed. The result is stored in the model like any other column, takes memory, and can be used on a visual axis, in a slicer or in a relationship.

DAX
Line Total = Sales[Quantity] * Sales[UnitPrice]

Inside a calculated column, a column reference means "this row's value". That is called row context, and the next lessons spend a lot of time on it.

Good uses: a label for grouping (Size Band = IF(Product[Price] > 100, "Premium", "Standard")), a key for a relationship, a column to slice by. Poor uses: anything you plan to add up with different filters. For that, use a measure.

Measures

A measure is not stored. Power BI evaluates it each time a visual needs it, for each cell of the chart, using the filters that apply to that cell. A card with no filters sees all rows; a bar for North sees only the rows of the North region; a bar for North in 2025 sees only those rows.

DAX
Total Sales = SUM(Sales[Amount])
Order Count = DISTINCTCOUNT(Sales[OrderID])
Average Order Value = DIVIDE([Total Sales], [Order Count])

Measures can call other measures, which is how you build a library of reusable pieces. Measures use almost no memory, and they respond to slicers, filters and every other chart interaction. This is why the rule of thumb is:

Use a calculated column when you need a value per row to slice by or relate on. Use a measure when you need a number to add up, average or compare.

A frequent beginner mistake is making a calculated column Margin = Sales[Amount] - Sales[Cost] and then summing it, or worse, making Margin % a column and averaging it. Averaging a column of percentages gives the wrong answer; a ratio of totals, DIVIDE(SUM(...), SUM(...)), gives the right one.

Calculated tables

A calculated table is a table produced by a DAX expression. It is evaluated at refresh, stored in memory, and then behaves like any other table.

DAX
Date = CALENDAR(DATE(2024, 1, 1), DATE(2026, 12, 31))

Top Customers =
TOPN(10, Customer, [Total Sales], DESC)

The most common use is the date table from Date Tables and Time Intelligence Setup. Others are helper tables for what-if parameters and slicers. Because they are stored, calculated tables increase the file size; prefer building tables in Power Query when the data can come from the source.

Quick measures

On the Home ribbon, Quick measure opens a gallery of common patterns (running total, year-over-year change, weighted average, percent difference from a filter). You pick the inputs by dragging fields and Power BI writes the DAX for you.

Treat the result as a learning tool: read the generated formula, rename the measure and tidy it. Quick measures follow good patterns, but you should be able to read and maintain what ends up in your model.

Measures vs columns in pandas

The same distinction exists in pandas, and seeing it in code helps. A calculated column is a new column computed for each row. A measure is a function you call on whichever rows survive a filter. This is a stand-in, not DAX: pandas has no filter context, so we pass the filtered rows to the function by hand.

Python Column per row, measure per filter

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The numbers for North and South add up to the card total. That is the behavior of a good measure: it gives the right answer at every level of detail.

Why averaging a percentage column fails

Here is the mistake from earlier, made visible. We compare an "average of row percentages" with a "ratio of totals".

Python Ratio of totals vs average of ratios

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The second row is small but has a large margin of 80%, and it distorts the plain average (45.0% against the true 16.4%). The right answer weighs each row by its revenue, which is exactly what a measure written as a ratio of sums does. This is the strongest argument for measures: define the logic once, as a ratio of aggregates, and every visual gets it right.

Naming and style

A few habits that keep a model readable:

  • Measures: no table prefix when you call them ([Total Sales]), and a clear business name (Total Sales, Order Count).
  • Columns: always with the table prefix (Sales[Amount]).
  • Put measures in a dedicated table or display folder (see Model View, Hierarchies, Formatting and Display Folders).
  • Use VAR for anything used twice, and indent so RETURN lines up.
  • Format measures once, in the model, instead of in every visual.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which is stored in the model and calculated once per row at refresh?

    A calculated column is evaluated for each row during refresh and stored. A measure is calculated on demand for each visual cell.
  2. 2. You need a Margin % card that stays correct when a slicer filters the data. What should you write?

    A ratio of aggregates inside a measure recomputes for whichever rows are in scope, and it weights each row correctly.
  3. 3. What does Sales[Amount] mean in a formula?

    A column reference is the table name followed by the column in square brackets. A measure reference has no table name.
  4. 4. Which operator joins two pieces of text?

    The ampersand concatenates text in DAX. && is logical AND and || is logical OR.
  5. 5. Why is it good practice to read the DAX that a quick measure writes?

    Quick measures are a shortcut, not a black box. Reading and tidying the result teaches you good patterns and keeps the model maintainable.

Summary

  • A DAX definition is Name = expression; columns are Table[Column], measures are [Name].
  • Calculated columns run per row and are stored; measures run per visual cell and react to filters; calculated tables are stored whole tables.
  • Use columns to slice and relate, measures to add up and compare.
  • Ratios belong in measures as a ratio of sums, never as an average of a ratio column.
  • VAR/RETURN, clear names and display folders keep formulas readable.

Next, the aggregation and iterator functions that power most measures: Aggregators and Iterators.