Two Different Kinds of DAX Calculation
Power BI supports two distinct ways to write DAX formulas that produce new values: calculated columns, which evaluate once per row using row context and are physically stored in the model like any other column, and measures, which contain no stored value at all and instead recalculate on the fly every time they're placed in a visual, using whatever filter context that visual currently applies. Choosing between them isn't a style preference — it changes when the calculation runs, how much memory it consumes, and whether it can respond dynamically to slicers and cross-filtering.
Cricket analogy: This is like the difference between a player's printed career batting average in a program (fixed once, printed for everyone to read) versus a live scoreboard average that recalculates instantly as the current innings unfolds.
Calculated Columns: Row Context and Storage
A calculated column is evaluated one row at a time, in row context, meaning the formula can directly reference other columns in the same row of the same table — useful for things like concatenating a First Name and Last Name column, or bucketing an Age column into age ranges. Because the result is computed once at refresh time and stored physically in the VertiPaq column store for every row, calculated columns increase the model's memory footprint and file size, and they cannot react to slicer selections the way a measure can, since their value was already fixed before any user interaction occurred.
Cricket analogy: This is like printing a 'Strike Rate' column directly into a match scorecard at the end of the game — computed once per row for each batsman and fixed on the page, unable to change even if a viewer later filters to only sixes.
// Calculated column: evaluated once per row, stored physically in the table
Age Bracket =
SWITCH (
TRUE (),
Customers[Age] < 25, "Under 25",
Customers[Age] < 45, "25-44",
Customers[Age] < 65, "45-64",
"65+"
)Calculated columns are appropriate when you need to slice or group visuals by the result (for example, putting Age Bracket on an axis or in a slicer), because slicers and axes need a static column value to filter on — a measure cannot be placed directly into a slicer's field well.
Measures: Filter Context and Dynamic Evaluation
A measure has no fixed value of its own; it's a formula that gets evaluated fresh every time it appears in a visual, aggregating rows using whatever filter context the report currently applies — the combination of slicers, visual-level filters, row and column headers, and cross-highlighting from other visuals on the page. This makes measures the right tool for anything that must respond dynamically to user interaction, like Total Sales, Year-over-Year Growth %, or a ratio like Profit Margin, and because nothing is stored, measures add essentially zero size to the model file regardless of how many you define.
Cricket analogy: This is like a live 'Run Rate Required' graphic on a broadcast that recalculates instantly with every ball bowled, responding to the current filter context of overs remaining and current score rather than being fixed in advance.
// Measure: no stored value, recalculated in the current filter context every time it's used
Total Sales = SUM ( Sales[SalesAmount] )
YoY Sales Growth % =
VAR CurrentSales = [Total Sales]
VAR PriorYearSales = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN
DIVIDE ( CurrentSales - PriorYearSales, PriorYearSales )Avoid using a calculated column to solve something a measure could handle, especially aggregations like SUM or ratios like margin percentage. Calculated columns bloat the model's storage and never respond to cross-filtering the way a measure does, so they should be reserved for values genuinely needed as static row-level attributes for filtering, sorting, or grouping.
- Calculated columns evaluate once per row at refresh time and are stored physically in the model.
- Measures have no stored value and recalculate dynamically every time they're used, based on filter context.
- Calculated columns use row context, letting a formula reference other columns in the same row directly.
- Measures use filter context, aggregating rows based on slicers, filters, and visual-level selections.
- Use calculated columns when the result must appear in a slicer, axis, or grouping (a static, filterable value).
- Use measures for dynamic aggregations like totals, ratios, and time-intelligence calculations.
- Overusing calculated columns for aggregation logic bloats model size and prevents dynamic filter response.
Practice what you learned
1. When is a calculated column's value computed?
2. Why can't a measure be placed directly into a slicer's field well?
3. What context does a calculated column formula use to reference other columns in the same row?
4. What is a key downside of using calculated columns for aggregation logic that a measure could handle instead?
5. Which of the following is the more appropriate use case for a measure rather than a calculated column?
Was this page helpful?
You May Also Like
Relationships and Star Schema
Understand how Power BI relationships work and why a star schema of fact and dimension tables is the recommended foundation for accurate, performant models.
Power Query Basics
Learn how Power Query lets you connect, shape, and clean data before it ever reaches your Power BI model, using a repeatable, no-code ETL workflow.
Data Refresh
Understand how Power BI keeps report data current through scheduled, incremental, and real-time refresh strategies, and the role of the on-premises data gateway.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics