Why this matters
As a BI Developer, you must choose the right DAX tool for the job. Measures and calculated columns can produce similar numbers, but they behave very differently. The right choice affects report flexibility, model size, refresh speed, and how visuals respond to filters and slicers.
- Pricing dashboards: use measures for dynamic KPIs that change with slicers.
- Data modeling: use calculated columns for row-level attributes like flags and bands.
- Performance: avoid bloating your model with unnecessary columns when a measure will do.
Concept explained simply
- Calculated column: computed during data refresh, stored in the table, one value per row. Does not change when a user clicks a slicer (except indirectly via relationships when referenced by measures).
- Measure: computed at query time, not stored, depends on the current filter context. Changes instantly with slicers, filters, and row/column placements in visuals.
Mental model
Think of a calculated column as writing a new value into each row of your table during refresh. It’s like adding a new field in your data warehouse.
Think of a measure as a calculator you run on-demand for the current slice of data the user is looking at. No rows are permanently changed.
When to use which
- Use a calculated column when you need: row-level attributes, sorting by a custom key, grouping/banding, relationships/keys, role labels that do not need to change with slicers.
- Use a measure when you need: totals, averages, ratios, time intelligence (YTD, rolling), dynamic KPIs that answer “for the current filters, what is X?”
Rule of thumb
- If you’d store it as a field in a table, it’s probably a calculated column.
- If it must respond to slicers and visuals, it’s probably a measure.
Worked examples (3)
Example 1 — Total Sales: measure vs column
Sample columns: Sales[Quantity], Sales[Unit Price].
Calculated column (row-level sales):
Sales[Line Sales] = Sales[Quantity] * Sales[Unit Price]Use when you need to aggregate many ways but also need per-row values (e.g., sort by Line Sales or create bands).
Measure (dynamic total):
Total Sales = SUMX(Sales, Sales[Quantity] * Sales[Unit Price])Use for visuals, cards, and ratios that must respond to slicers (by date, region, product).
Example 2 — Profit Margin %: measure vs column
Sample columns: Sales[Unit Price], Sales[Unit Cost], Sales[Quantity].
Calculated column (row-level margin %):
Sales[Row Margin %] = DIVIDE(Sales[Unit Price] - Sales[Unit Cost], Sales[Unit Price])Good for classifying rows into margin bands.
Measures (dynamic totals and margin %):
Total Revenue = SUMX(Sales, Sales[Quantity] * Sales[Unit Price])
Total Cost = SUMX(Sales, Sales[Quantity] * Sales[Unit Cost])
Margin % = DIVIDE([Total Revenue] - [Total Cost], [Total Revenue])The Margin % measure updates with any filter (e.g., by month or product) without extra storage in the model.
Example 3 — High-value customer: column vs measure
Goal: Flag customers who have ever purchased over 1000 in a single transaction and show dynamic counts.
Calculated column in Sales table (per row):
Sales[Row Amount] = Sales[Quantity] * Sales[Unit Price]Calculated column in Customer table (requires relationship):
Customer[High Value] =
VAR MaxOrder = CALCULATE(MAX(Sales[Row Amount]))
RETURN IF(MaxOrder >= 1000, "Yes", "No")Measure for dynamic count of high-value customers:
High Value Customers =
CALCULATE(DISTINCTCOUNT(Customer[CustomerID]), Customer[High Value] = "Yes")The measure responds to time or region slicers instantly; the column defines a stable attribute on the Customer table.
Performance and storage tips
- Every calculated column increases model size; prefer measures if you only need aggregated results.
- Use calculated columns for sort-by columns, keys, and bands—values that are reused across visuals.
- Avoid materializing heavy row-level expressions if you only consume the number as a total in visuals—use measures instead.
Hands-on exercises
Work through the exercises below. Use the checklist to self-verify. Solutions are available in each exercise’s “Show solution” panel.
Exercise 1 — Total Sales measure vs Discounted Sales column
Create:
- A measure for Total Sales.
- A calculated column for Sales After Discount.
Mini data you can imagine
Row | Qty | Unit Price | Discount Rate
1 | 2 | 30 | 0.10
2 | 1 | 100 | 0.00
3 | 5 | 12 | 0.05- Make a card visual with Total Sales.
- Make a table showing Sales After Discount per row.
- Checklist:
- Total Sales changes with filters.
- Sales After Discount is stored per row and sums correctly.
Exercise 2 — Margin % measure and Margin Band column
Create:
- A Margin % measure using totals.
- A Margin Band calculated column using row-level margin % thresholds.
Mini data you can imagine
Row | Qty | Unit Price | Unit Cost
1 | 2 | 50 | 35
2 | 3 | 20 | 10
3 | 1 | 200 | 140- Put Margin % on a card and segment by product.
- Use Margin Band to color or group products.
- Checklist:
- Margin % changes with product filters.
- Margin Band stays the same regardless of slicers (it’s row-based).
Common mistakes and self-check
- Expecting calculated columns to change with slicers. Self-check: Slice by date; does your column update? It shouldn’t.
- Using a column when a measure was enough, inflating model size. Self-check: Do you only ever show the total? Use a measure.
- Building measures from row context accidentally. Self-check: Use SUMX over a table for expressions that multiply columns (e.g., Qty * Price).
- Creating ranking as a calculated column that must respond to slicers. Self-check: If rank must react to filters, implement rank as a measure instead.
Practical projects
- Sales dashboard: measures for Total Sales, Total Cost, Margin %, YoY Growth; columns for Product Category Sort Key and Price Band.
- Customer 360: measures for Active Customers, Average Order Value; columns for Customer Segment and First Purchase Month (for sorting).
- Inventory report: measures for Days of Supply and Stockout Rate; columns for Reorder Flag by static threshold.
Who this is for
- BI Developers and Analysts building Power BI models.
- Anyone deciding between dynamic metrics and stored attributes in DAX.
Prerequisites
- Basic data modeling: tables, relationships, keys.
- Intro DAX: SUM, SUMX, CALCULATE, DIVIDE.
- Familiarity with Power BI Desktop or similar tools that run DAX.
Learning path
- Master filter vs row context basics.
- Practice with SUMX and CALCULATE to build robust measures.
- Create common attribute columns: bands, flags, sort keys.
- Add time intelligence measures for real dashboards.
- Optimize model size by replacing unnecessary columns with measures.
Mini challenge
Given Sales[Date], Sales[Product], Sales[Qty], Sales[Unit Price], Sales[Unit Cost]:
- Create a measure: Margin %.
- Create a column: Price Band with thresholds (<=20 Low, <=100 Mid, >100 High).
- Build a bar chart of Margin % by Price Band and use a date slicer. Confirm the band stays static while Margin % changes.
Hint
Margin % should use totals (SUMX). Price Band should read Unit Price per row.
Next steps
- Refactor one of your reports: replace any “sum of a row expression” columns with a SUMX measure.
- Add sort-by columns where needed for correct visuals.
- Document your model: list which items are measures vs columns and why.
Quick Test
Take the quick test below to check understanding. Anyone can take it for free. If you log in, your progress and score are saved.