DAX Measures in Power BI: Writing Calculations That Actually Work

What DAX Is

DAX (Data Analysis Expressions) is the formula language used in Power BI, Analysis Services, and Power Pivot. Unlike Excel formulas (which operate on cells) or SQL (which operates on rows), DAX is designed to operate within filter contexts — the combination of filters applied by slicers, row/column context in a matrix, or explicit CALCULATE filters.

Understanding filter context is the key to writing DAX correctly. Most DAX bugs are context bugs — the measure is computing the right calculation, but in the wrong context.

Measures vs Calculated Columns

**Calculated columns** are computed row-by-row during refresh and stored in the model. They are evaluated in row context — the current row's values are available. Appropriate for: categorising rows based on their values, computing row-level flags, deriving attributes.

**Measures** are computed at query time in response to filter context. They do not exist as stored data — they are formulas that run when the report needs them. Appropriate for: aggregations, ratios, time intelligence, anything that should respond to slicer selections.

Rule: if the calculation should change when a slicer is used, it is a measure. If it should be the same value for each row regardless of what slicers are applied, it might be a calculated column.

Basic Measure Syntax

Measures are created in the semantic model using DAX:

Total Revenue = SUM(orders[order_amount])

Order Count = COUNTROWS(orders)

Average Order Value = DIVIDE([Total Revenue], [Order Count])

Unique Customers = DISTINCTCOUNT(orders[customer_id])

DIVIDE is preferred over the / operator because DIVIDE returns BLANK() on division by zero instead of an error.

Filter Context and Evaluation Context

Every DAX measure is evaluated in a filter context. The filter context is the intersection of all filters currently active: which rows are visible in the filter.

When a measure is placed in a table visual with Region on rows, Power BI evaluates the measure once per region — applying a filter context that includes only that region's rows for each calculation. The same measure Total Revenue returns the total across all regions when placed in a card with no filter, and returns each region's revenue when in a table.

**Row context vs filter context**:

- Calculated columns run in row context (current row available, no filter context)

- Measures run in filter context (filtered table visible, no specific row context)

- CALCULATE converts row context to filter context via context transition — important for measures used inside SUMX or CALCULATE with iterator functions

CALCULATE: The Most Important DAX Function

CALCULATE evaluates an expression in a modified filter context. It is the mechanism for overriding or adding to the current filter context.

CALCULATE(expression, filter1, filter2, ...)

**Filtering to a subset**:

West Region Revenue = CALCULATE([Total Revenue], orders[region] = "West")

Returns total revenue filtered to West only, regardless of what region slicer is selected. This is an absolute filter — it ignores the current slicer filter and replaces it.

**Removing a filter**:

All Region Revenue = CALCULATE([Total Revenue], ALL(orders[region]))

ALL removes the region filter from the filter context. Revenue for All Regions ignores whatever region slicer is selected.

**Combining filters**:

West Prior Year Revenue = CALCULATE(

[Total Revenue],

orders[region] = "West",

DATEADD(dates[date], -1, YEAR)

)

Multiple CALCULATE arguments are combined with AND logic — the result is filtered to West AND to the prior year.

Time Intelligence Functions

DAX has a rich library of time intelligence functions that require a proper date table marked as a Date Table in the model:

**Year-to-date**:

Revenue YTD = TOTALYTD([Total Revenue], dates[date])

Equivalent to: CALCULATE([Total Revenue], DATESYTD(dates[date]))

**Prior year comparison**:

Revenue PY = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(dates[date]))

YoY Change = [Total Revenue] - [Revenue PY]

YoY Pct = DIVIDE([YoY Change], [Revenue PY])

**Month-over-month**:

Revenue MoM = [Total Revenue] - CALCULATE([Total Revenue], DATEADD(dates[date], -1, MONTH))

**Rolling 12 months**:

Revenue L12M = CALCULATE([Total Revenue], DATESINPERIOD(dates[date], MAX(dates[date]), -12, MONTH))

Note: DATESINPERIOD uses the MAX of the current date context as the end date. In a matrix with month columns, this gives the 12 months ending at each displayed month.

Common DAX Patterns

**% of total**:

Revenue % of Total = DIVIDE([Total Revenue], CALCULATE([Total Revenue], ALL(orders)))

Revenue as a percentage of the total across all dimensions. CALCULATE ALL removes the current filter context for orders entirely, so the denominator is always the grand total.

**% of parent (row total)**:

Revenue % of Region = DIVIDE(

[Total Revenue],

CALCULATE([Total Revenue], ALLSELECTED(orders[product]))

)

ALLSELECTED removes the product filter (from the current row) but keeps other active filters (region slicer, date slicer). The denominator is the total for the current region selection.

**Cumulative total**:

Revenue Cumulative = CALCULATE(

[Total Revenue],

FILTER(

ALL(dates[date]),

dates[date] <= MAX(dates[date])

)

)

FILTER creates a table of all dates up to and including the current date in the visual context. CALCULATE applies that as the date filter.

**Rank within filter context**:

Product Revenue Rank = RANKX(

ALL(products[product_name]),

[Total Revenue],

,

DESC,

DENSE

)

RANKX evaluates [Total Revenue] for each product in ALL(products), then ranks the current product's revenue. Responds to filter context — the rank changes when region or date slicers are applied.

DAX Performance Considerations

**Avoid calculated columns for aggregations**: Storing aggregated values in calculated columns duplicates work that measures do more efficiently. Use measures.

**Use SUMMARIZE or ADDCOLUMNS carefully**: SUMMARIZE is expensive — prefer ADDCOLUMNS(SUMMARIZE(...), ...) over SUMMARIZE with new columns. For most aggregations, direct measures on base tables are faster.

**VAR/RETURN for complex measures**: Use variables to avoid computing the same expression multiple times within a measure:

YoY % =

VAR current_revenue = [Total Revenue]

VAR prior_revenue = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(dates[date]))

RETURN DIVIDE(current_revenue - prior_revenue, prior_revenue)

Variables are evaluated once, improving both performance and readability.

**Use DAX Studio for analysis**: DAX Studio is the free tool for writing and profiling DAX queries, viewing server timing, and diagnosing slow measures. SSMS-style query window that connects to Power BI Desktop or Premium models via XMLA.

Our BI strategy practice designs Power BI semantic models and DAX calculations for enterprise reporting — contact us to discuss your Power BI requirements.

A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.