4.4 Calculated Columns and Calculated Tables
Key Takeaways
- Calculated columns compute row-by-row in DAX and are stored in the model, increasing its size; they recompute on refresh.
- Calculated tables create whole tables from DAX, commonly date tables (CALENDAR/CALENDARAUTO) and summary tables.
- Prefer Power Query computed columns over DAX calculated columns when the source data is available, because they fold to the source and add no DAX overhead.
- Calculated columns evaluate in row context and can feed slicers, filters, sort, and relationships; measures cannot.
- Use a measure instead of a calculated column whenever the result must respond to filter context or aggregate.
Quick Answer: Calculated columns add columns to existing tables using DAX (evaluated row-by-row, stored in the model). Calculated tables create new tables using DAX (such as CALENDAR() for a date table). Prefer Power Query for column computations when possible; use a DAX calculated column only when you must reference the data model (relationships, other tables).
Calculated Columns
A calculated column adds a new column whose value DAX computes for each row. Create one with Table Tools > New Column.
Full Name = [FirstName] & " " & [LastName]
Price Tier = IF([UnitPrice] > 100, "Premium", IF([UnitPrice] > 50, "Standard", "Budget"))
Category Name = RELATED(Products[Category])
Order Year = YEAR([OrderDate])
Profit = [Revenue] - [Cost]
Calculated columns evaluate row-by-row during refresh (row context), are stored in the model (consuming memory), recompute on each refresh, and can be used in slicers, filters, relationships, and visuals. They are not available on DirectQuery-only tables in the same way Import columns are.
When to Use a Calculated Column
| Use Case | Why |
|---|---|
| Reference a related table | RELATED() needs row context |
| Build a key for a relationship | Relationships need physical columns |
| Complex logic needing model data | Nested IF/SWITCH over related tables |
| Column must traverse relationships | Cannot be done in Power Query |
When NOT to Use a Calculated Column (Use Power Query)
| Use Case | Why Power Query Is Better |
|---|---|
| Simple text manipulation | Text.Upper, Text.Trim in M |
| Date extraction | Date.Year, Date.Month in M |
| Concatenation | & operator in M |
| Conditional logic on source data | if-then-else in M |
| Static lookups | Merge Queries |
Power Query columns are processed during refresh rather than stored as DAX overhead, benefit from query folding (the transformation is pushed to the source database), keep the DAX engine lean, and separate ETL logic from analytics. As a rule, transform in Power Query, model in DAX.
Calculated Tables
A calculated table creates an entirely new table from a DAX expression (Modeling > New Table).
Date =
VAR StartDate = DATE(2020, 1, 1)
VAR EndDate = DATE(2026, 12, 31)
RETURN
ADDCOLUMNS(
CALENDAR(StartDate, EndDate),
"Year", YEAR([Date]),
"Month Number", MONTH([Date]),
"Month Name", FORMAT([Date], "MMMM"),
"Day of Week", FORMAT([Date], "dddd"),
"Year-Month", FORMAT([Date], "YYYY-MM")
)
CALENDARAUTO() auto-spans the full range of dates across all date columns in the model, so Date = CALENDARAUTO() builds a contiguous calendar without explicit bounds. Other patterns: DISTINCT(Products[Category]) for a distinct-values table, and SUMMARIZE(...) for a summary table.
Calculated Table Characteristics
| Property | Behavior |
|---|---|
| Storage | Fully materialized in the model |
| Refresh | Re-evaluated on every data refresh |
| Relationships | Can participate in relationships |
| Measures | Can host measures |
| Limitation | Not available in DirectQuery mode |
Decision Tree for a New Column or Table
- Computed from source data alone? Use Power Query.
- Needs related tables via relationships? Use a calculated column (DAX).
- Needs to aggregate or respond to filters? Use a measure instead.
- Need a whole new standalone table? Use a calculated table (DAX).
The most common exam mistake is creating a calculated column where a measure belongs. If the value must change as users slice and filter (a total, a ratio, a running sum), it is a measure, not a column. A calculated column produces one fixed value per row at refresh time and never reacts to filter context.
On the Exam
The PL-300 tests choosing between calculated columns and measures, knowing when Power Query beats DAX for column computations, building a date table with CALENDAR()/CALENDARAUTO(), recognizing that calculated columns run in row context, and that they increase model size.
The Mental Model: Three Places to Compute
Every new value in a Power BI model can be created in one of three layers, and choosing the right layer is a core exam skill. Power Query (M) transforms data during load and is the right home for cleansing, type changes, derived attributes from source columns, merges, and any work that can fold to the source. Calculated columns (DAX) add stored, row-context values that depend on model relationships, the place for RELATED() lookups and keys you cannot build at the source. Measures (DAX) compute dynamically in filter context and are the right home for anything that aggregates or must react to slicers.
A clean model pushes work as far upstream as possible: transform in Power Query, model with calculated columns only when relationships are required, and express analytics as measures.
Why Calculated Columns Cost More Than They Look
A DAX calculated column is materialized and stored, so it consumes memory and is included in the model's compression. Worse, it is computed after Power Query load and after relationships are built, so it cannot fold to the source and runs entirely in the analytics engine on refresh. A column that could have been a folded Power Query step therefore both bloats the model and lengthens refresh. The exam rewards recognizing this: when a derived column depends only on columns in the same table or its source, Power Query is the efficient answer; reserve DAX calculated columns for genuine cross-table, relationship-dependent logic.
Calculated Tables in Practice
Beyond date tables, calculated tables are useful for what-if parameters (a disconnected table of values a user selects with a slicer), role-playing date copies, and summary/bridge tables built with SUMMARIZE, ADDCOLUMNS, DISTINCT, UNION, or CROSSJOIN. Because they re-evaluate on every refresh, keep their logic lean. A frequent design choice on the exam: build a small disconnected parameter table as a calculated table, then reference its selected value in a measure with SELECTEDVALUE to drive what-if analysis.
You need to create a Year column from an OrderDate column. The data comes from a SQL Server database. What is the most efficient approach?
Which DAX function automatically creates a date table spanning all dates found in date columns throughout the model?
A calculated column uses RELATED() to pull the Category from a Products dimension into the Sales fact table. Why is RELATED() needed?
You want a value that recalculates whenever users change slicers and filters. Should you use a calculated column or a measure?