4.4 Calculated Columns and Calculated Tables
Key Takeaways
- Calculated columns are computed row-by-row using DAX and stored in the model — they increase model size.
- Calculated tables are entire tables created using DAX expressions — common for date tables and summary tables.
- Prefer Power Query computed columns over DAX calculated columns when the source data is available during load.
- Calculated columns evaluate in row context and can be used for filtering, sorting, and relationships.
- Calculated tables are re-evaluated on data refresh, not on every query.
Calculated Columns and Calculated Tables
Quick Answer: Calculated columns add new columns to existing tables using DAX (evaluated row-by-row, stored in model). Calculated tables create new tables using DAX (like CALENDAR() for date tables). Prefer Power Query for column computations when possible — it's more efficient. Use DAX calculated columns only when you need to reference the data model (relationships, measures).
Calculated Columns
A calculated column adds a new column to an existing table. It uses DAX to compute a value for each row.
Creating a Calculated Column
Table Tools → New Column → Enter DAX formula
Example formulas:
// Concatenation
Full Name = [FirstName] & " " & [LastName]
// Conditional logic
Price Tier =
IF([UnitPrice] > 100, "Premium",
IF([UnitPrice] > 50, "Standard", "Budget"))
// Referencing related tables
Category Name = RELATED(Products[Category])
// Date extraction
Order Year = YEAR([OrderDate])
// Mathematical calculation
Profit = [Revenue] - [Cost]
How Calculated Columns Work
- Evaluated row-by-row during data refresh (row context)
- Values are stored in the model (increases memory usage)
- Re-calculated on each data refresh
- Can be used in slicers, filters, relationships, and visuals
- Cannot be used in DirectQuery tables (Import only)
When to Use Calculated Columns
| Use Case | Why |
|---|---|
| Need to reference a related table | RELATED() function requires row context |
| Creating a column for relationships | Key columns for model relationships |
| Complex DAX logic not possible in Power Query | Nested IF, SWITCH with model data |
| Column depends on model relationships | Needs to traverse relationships |
When NOT to Use Calculated Columns (Use Power Query Instead)
| 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 |
Why Power Query is preferred:
- Processed during refresh, not stored as DAX overhead
- Benefits from query folding (pushed to source)
- Doesn't increase the DAX engine workload
- Better separation of ETL and analytics logic
Calculated Tables
A calculated table creates an entirely new table using a DAX expression.
Creating a Calculated Table
Modeling tab → New Table → Enter DAX formula
Common Calculated Table Patterns
1. Date Table using CALENDAR()
Date =
VAR StartDate = DATE(2020, 1, 1)
VAR EndDate = DATE(2026, 12, 31)
RETURN
ADDCOLUMNS(
CALENDAR(StartDate, EndDate),
"Year", YEAR([Date]),
"Quarter", "Q" & FORMAT([Date], "Q"),
"Month Number", MONTH([Date]),
"Month Name", FORMAT([Date], "MMMM"),
"Day of Week", FORMAT([Date], "dddd"),
"Day of Week Number", WEEKDAY([Date], 2),
"Year-Month", FORMAT([Date], "YYYY-MM"),
"Is Weekend", IF(WEEKDAY([Date], 2) >= 6, TRUE, FALSE)
)
2. Date Table using CALENDARAUTO()
Date = CALENDARAUTO()
CALENDARAUTO() automatically spans the full range of dates found across all date columns in the model.
3. Distinct Values Table
Unique Categories = DISTINCT(Products[Category])
4. Summary Table
Product Summary =
SUMMARIZE(
Sales,
Products[ProductName],
"Total Sales", SUM(Sales[Amount]),
"Order Count", COUNTROWS(Sales)
)
Calculated Table Characteristics
| Property | Behavior |
|---|---|
| Storage | Fully materialized in the model |
| Refresh | Re-evaluated on every data refresh |
| Relationships | Can participate in model relationships |
| Measures | Can contain measures defined on them |
| Limitations | Not available in DirectQuery mode |
Best Practice Decision Tree
When you need a new column:
- Can it be computed from source data alone? → Use Power Query
- Does it need to reference related tables via relationships? → Use Calculated Column (DAX)
- Does it need to aggregate or use filter context? → Use a Measure instead
- Do you need a new standalone table? → Use Calculated Table (DAX)
On the Exam
The PL-300 frequently tests:
- Choosing between calculated columns and measures for a scenario
- Knowing when to use Power Query vs. DAX for column computations
- Creating a date table using CALENDAR() or CALENDARAUTO()
- Understanding that calculated columns operate in row context
- Recognizing that calculated columns increase model size
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 the RELATED() function to pull the Category name from a Products dimension table into the Sales fact table. Why is RELATED() needed here instead of a simple column reference?