Power Platform/Learn/Power BI Expressions

Power BI Expressions (DAX)

DAX (Data Analysis Expressions) is the formula language for Power BI. This guide takes you from the very basics: what DAX is and when to use it: through to CALCULATE, time intelligence, and the patterns every report builder needs.

DAX vs M (Power Query): Which is which?

Power BI has two formula languages and beginners often confuse them. Understanding the boundary is the single most important conceptual step.

DAX: Data Analysis Expressions

Used in: Measures, calculated columns, calculated tables
Runs when: A visual renders or a filter changes: at query time
Good for: Calculations, aggregations, KPIs, ratios, time comparisons
Where you write it: Inside Power BI Desktop in the formula bar

M: Power Query Formula Language

Used in: Power Query transformations (the ETL step)
Runs when: Data is refreshed: at load time
Good for: Cleaning, filtering, merging, and shaping data before it loads
Where you write it: Power Query Editor (Transform Data)

The rule of thumb: if you are shaping or cleaning data before it enters the model, use M in Power Query. If you are performing a calculation that depends on what filters are applied in a report, use DAX.

Measures vs Calculated Columns

Within DAX, there are two places to write formulas: measures and calculated columns. Understanding when to use each is critical: getting it wrong causes slow reports and incorrect results.

Measures: Dynamic calculations

A measure is a formula that is evaluated at query time based on the current filter context. Every time the user changes a slicer, drills down, or applies a filter, every measure on the visual is recalculated. Measures never store data: they compute on the fly.

Creating a measure: click New Measure in the ribbon

Total Sales = SUM(Sales[Amount])

Average Price = AVERAGE(Sales[UnitPrice])

Sales Count = COUNTROWS(Sales)

Calculated Columns: Static per-row values

A calculated column adds a new column to an existing table. It is evaluated row-by-row when the data loads and the result is stored in the model. It does not respond to report filters the value is fixed at load time.

Creating a calculated column: click New Column in the ribbon

Full Name = Sales[FirstName] & " " & Sales[LastName]

Margin % = DIVIDE(Sales[Profit], Sales[Revenue])

Category Band = IF(Sales[Amount] > 1000, "Large", "Small")

	Measure	Calculated Column
Stores data?	No: computed on demand	Yes: stored per row
Responds to filters?	Yes: always	No: fixed at load time
Works in visuals?	Yes: dragged to Values	Yes: dragged to Rows/Columns
Use for?	KPIs, totals, ratios, YTD	Row-level labels, categories, lookups
Performance impact?	Query time (scales with visual)	Load time (inflates model size)

The golden rule: prefer measures over calculated columns whenever possible. Measures are more flexible, don't bloat your model, and respond correctly to all filters. Only use a calculated column when you genuinely need a fixed, per-row value: like a concatenated label or a lookup result.

Basic aggregation functions

These are the building blocks. Every DAX developer uses these every day.

DAX Formula

Total Revenue = SUM(Sales[Amount])

Returns

Sum of all values in the Amount column (respects current filter context)

DAX Formula

Average Order Value = AVERAGE(Sales[Amount])

Returns

Mean value of Amount for the current context

DAX Formula

Order Count = COUNT(Sales[OrderID])

Returns

Count of non-blank values in OrderID

COUNT only counts numbers. Use COUNTA for text columns, COUNTROWS for rows.

DAX Formula

Customer Count = DISTINCTCOUNT(Sales[CustomerID])

Returns

Number of unique customers in the current context: essential for headcount metrics

DAX Formula

Max Sale = MAX(Sales[Amount])

Returns

Highest single sale value

DAX Formula

Min Sale = MIN(Sales[Amount])

Returns

Lowest single sale value

DIVIDE: safe division

Never use the / operator directly. If the denominator is zero, it throws a division-by-zero error. DIVIDE(numerator, denominator, alternateResult) returns the alternate result (default: blank) instead of an error.

DAX Formula

Win Rate = DIVIDE(COUNTROWS(Deals), COUNTROWS(ALL(Deals)))

Returns

0.38 (38% of all deals closed as won in the current context)

CALCULATE: The most important DAX function

CALCULATE is the function that makes DAX powerful. It evaluates an expression inside a modified filter context. In plain English: "calculate this thing, but change which rows are included when doing so."

Every use of CALCULATE follows the same pattern:

CALCULATE(
  <expression>,        -- what to calculate
  <filter1>,           -- which rows to include (optional)
  <filter2>,           -- additional filters (all are ANDed)
  ...
)

Example: Sales for a specific category

DAX Formula

Electronics Sales =
CALCULATE(
    SUM(Sales[Amount]),
    Sales[Category] = "Electronics"
)

Returns

Total sales where Category is Electronics, regardless of what the user has filtered in the report

Example: Sales only for the current year (overriding report filter)

DAX Formula

Current Year Sales =
CALCULATE(
    SUM(Sales[Amount]),
    YEAR(Sales[Date]) = YEAR(TODAY())
)

Returns

Sum of sales for the current calendar year only

CALCULATE with ALL: removing filters

ALL() inside CALCULATE removes all filters from a table or column. This is essential for computing "% of total" metrics.

DAX Formula

% of Total Sales =
DIVIDE(
    SUM(Sales[Amount]),
    CALCULATE(SUM(Sales[Amount]), ALL(Sales))
)

Returns

0.23: this product's sales as a share of all sales, regardless of any category filter

Think of CALCULATE as having two jobs: it first applies any filter arguments you give it, then evaluates the expression. The filters can add, narrow, or completely replace the context from the visual. This is what makes DAX so expressive.

CALCULATE changes the filter context, not the row context. If you are inside a calculated column (row context), CALCULATE is what transitions that row context into a filter context for the aggregation. This is called context transition and is one of the trickier DAX concepts.

FILTER function

FILTER(table, condition) returns a subset of a table where the condition is true. It is usually used as an argument to CALCULATE when you need a more complex condition than a simple equality check.

DAX Formula

High Value Sales =
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(Sales, Sales[Amount] > 1000)
)

Returns

Sum of only the sales transactions over £1000

DAX Formula

Active Customer Revenue =
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(Customers, Customers[Status] = "Active")
)

Returns

Revenue from active customers only, using a filter on the Customers table

For simple column equality filters like Sales[Category] = "Electronics", you don't need FILTER: just pass the condition directly to CALCULATE. Use FILTER when your condition involves functions or comparisons on computed values.

When you have a data model with related tables (e.g. a Sales table related to a Customers table), these functions let you access values across the relationship.

RELATED: Look up a value from a related table (many-to-one)

Used in a calculated column to bring in a value from the "one" side of the relationship.

Calculated column in Sales table: looking up the customer's region

Customer Region = RELATED(Customers[Region])

RELATEDTABLE: Get the related rows (one-to-many)

Returns all rows from the related table that relate to the current row. Often used with COUNTROWS to count related records.

DAX Formula

Orders Per Customer = COUNTROWS(RELATEDTABLE(Orders))

Returns

3 (the number of orders linked to the current customer row)

Time intelligence

Time intelligence functions compare values across time periods: year-to-date, same period last year, rolling 3 months. They require a properly marked Date table in your model.

Before using any time intelligence function, you must have a Date table in your model that covers every date in your data, has no gaps, and is marked as a Date Table in Power BI Desktop (right-click the table → Mark as date table).

TOTALYTD: Year-to-date total

DAX Formula

Sales YTD =
TOTALYTD(SUM(Sales[Amount]), 'Date'[Date])

Returns

Cumulative sum of Sales from the start of the year to the current date in context

SAMEPERIODLASTYEAR: Previous year comparison

DAX Formula

Sales LY =
CALCULATE(
    SUM(Sales[Amount]),
    SAMEPERIODLASTYEAR('Date'[Date])
)

Returns

Sales for the same date range but in the previous year

Year-over-year change

DAX Formula

YoY Growth % =
DIVIDE(
    SUM(Sales[Amount]) - [Sales LY],
    [Sales LY]
)

Returns

0.12: 12% growth vs the same period last year

DATEADD: Shift dates by any period

More flexible than SAMEPERIODLASTYEAR: lets you shift by any number of days, months, quarters, or years.

DAX Formula

Sales 3 Months Ago =
CALCULATE(
    SUM(Sales[Amount]),
    DATEADD('Date'[Date], -3, MONTH)
)

Returns

Sales for the period 3 months before the current context

DATESYTD, DATESMTD, DATESQTD

Return a table of dates for year-to-date, month-to-date, or quarter-to-date. Used inside CALCULATE as the filter argument.

DAX Formula

MTD Sales =
CALCULATE(
    SUM(Sales[Amount]),
    DATESMTD('Date'[Date])
)

Returns

Sales from the 1st of the current month to the current date

Variables in DAX

DAX variables (VAR) store intermediate results inside a formula. They make complex measures readable, avoid repeating the same sub-expression multiple times, and can significantly improve performance by preventing the same calculation from running twice.

Using VAR to simplify a complex measure

GP Margin % =
VAR TotalRevenue = SUM(Sales[Revenue])
VAR TotalCOGS    = SUM(Sales[COGS])
VAR GrossProfit  = TotalRevenue - TotalCOGS
RETURN
    DIVIDE(GrossProfit, TotalRevenue)

Always end a VAR block with RETURN. The RETURN statement is what the measure actually produces: everything before it is just internal working.

Common DAX patterns

Running total

DAX Formula

Running Total =
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(
        ALL('Date'[Date]),
        'Date'[Date] <= MAX('Date'[Date])
    )
)

Returns

Cumulative sum from the earliest date up to the current date in the visual

Ranking: top N products

DAX Formula

Product Rank =
RANKX(
    ALL(Products[ProductName]),
    [Total Sales],
    ,
    DESC,
    DENSE
)

Returns

1 for best-selling product, 2 for second, etc.

Dynamic title showing the selected filter

DAX Formula

Report Title =
"Sales by " &
IF(
    ISFILTERED(Products[Category]),
    SELECTEDVALUE(Products[Category], "Multiple categories"),
    "All Categories"
)

Returns

'Sales by Electronics' when one category is selected, or 'Sales by All Categories'

Blank handling: ISBLANK and IF(ISBLANK(...))

DAX Formula

Safe Total =
IF(
    ISBLANK(SUM(Sales[Amount])),
    0,
    SUM(Sales[Amount])
)

Returns

0 instead of (Blank) when no rows match the current filter

Conditional formatting measure

DAX Formula

Traffic Light =
IF([Sales vs Target] >= 1, 1,      -- Green
IF([Sales vs Target] >= 0.8, 0,    -- Amber
-1))                                -- Red

Returns

1, 0, or -1 used in conditional formatting rules for colour-coded indicators

Quick reference

Function	What it does
SUM(column)	Sum of a numeric column
AVERAGE(column)	Mean value
COUNT(column)	Count of non-blank numbers
COUNTA(column)	Count of non-blank values (any type)
COUNTROWS(table)	Number of rows in a table or filter result
DISTINCTCOUNT(column)	Count of unique values
MIN(column) / MAX(column)	Smallest / largest value
DIVIDE(n, d, alt)	Safe division: returns alt if denominator is 0
CALCULATE(expr, filter...)	Evaluate expr in a modified filter context
ALL(table or column)	Remove all filters: used inside CALCULATE
FILTER(table, condition)	Return rows matching a condition
RELATED(column)	Bring in a value from a related table (many-to-one)
RELATEDTABLE(table)	Get all related rows (one-to-many)
TOTALYTD(expr, dates)	Year-to-date cumulative value
SAMEPERIODLASTYEAR(dates)	Equivalent date range in the prior year
DATEADD(dates, n, interval)	Shift date range by n periods
RANKX(table, expr)	Rank rows by a measure value
SELECTEDVALUE(column, alt)	Current single selected value in context
ISBLANK(value)	True if a value is blank
IF(cond, true, false)	Conditional branching
SWITCH(expr, val, result...)	Multi-branch conditional: cleaner than nested IF

Practice: Write the DAX

0 / 8 completed

Each exercise uses the same Sales table below. Read the goal, write the DAX measure definition, and the playground checks your formula structure in real time.

Practice in Power BI Desktop

The best way to learn DAX is to build. Open Power BI Desktop, connect to sample data (the AdventureWorks sample is built in), and write these measures yourself. Then use the Expression Reference to look up any PA expression functions you need in your flows.

PA Expressions

← Array Actions Back to Learn hub

Power BI Expressions (DAX)

DAX vs M (Power Query): Which is which?

Measures vs Calculated Columns

Measures: Dynamic calculations

Calculated Columns: Static per-row values

Basic aggregation functions

DIVIDE: safe division

CALCULATE: The most important DAX function

Example: Sales for a specific category

Example: Sales only for the current year (overriding report filter)

CALCULATE with ALL: removing filters

FILTER function

RELATED and RELATEDTABLE

RELATED: Look up a value from a related table (many-to-one)

RELATEDTABLE: Get the related rows (one-to-many)

Time intelligence

TOTALYTD: Year-to-date total

SAMEPERIODLASTYEAR: Previous year comparison

Year-over-year change

DATEADD: Shift dates by any period

DATESYTD, DATESMTD, DATESQTD

Variables in DAX

Common DAX patterns

Running total

Ranking: top N products

Dynamic title showing the selected filter

Blank handling: ISBLANK and IF(ISBLANK(...))

Conditional formatting measure

Quick reference

Practice: Write the DAX