3.1.5. Calculated Columns and Calculated Tables
💡 First Principle: Calculated columns and calculated tables are DAX objects that are computed during refresh and stored, consuming memory like imported data. They earn their cost when the result must exist before a query runs: as something you slice by, sort by, relate on, or as a whole table the model needs. Anything that should react to user filters belongs in a measure instead.
| Object | Evaluated | Stored | Recalculates when slicers change | Use when |
|---|---|---|---|---|
| Measure | At query time, in filter context | No | Yes | Aggregations, ratios, comparisons |
| Calculated column | At refresh, per row (row context) | Yes, in the table | No (its values are fixed at refresh, but the column itself can be used in a slicer) | Slicing, grouping, sorting, relationship keys, row-level categories |
| Calculated table | At refresh | Yes, as a table | No | Date tables, role-playing copies, summary or bridge tables, disconnected parameter tables |
Good calculated column use cases: an Age Band or Price Tier category used on a slicer; a Full Name concatenation for display; a sort key; a column that depends on related data via RELATED (bringing a Product's Category into Sales when it's genuinely needed for a relationship to another table). Many of these could also be done in Power Query, which is usually preferable (Roche's Maxim); choose DAX when the logic needs model relationships or other DAX results.
Good calculated table use cases: a date table (CALENDAR), role-playing dimension copies ('Ship Date' = 'Date'), a summary table for a specific visual (SUMMARIZE(Sales, 'Date'[Year], Product[Category], "Sales", [Total Sales])), a disconnected table of thresholds for what-if analysis, or a distinct list (DISTINCT(Sales[Channel])). Functions you should recognize: CALENDAR, CALENDARAUTO, SUMMARIZE, SUMMARIZECOLUMNS, ADDCOLUMNS, SELECTCOLUMNS, DISTINCT, VALUES, FILTER, UNION, CROSSJOIN, DATATABLE.
Creating them: in Report or Table view, choose New column (formula evaluated per row of the selected table) or New table (formula must return a table). In DirectQuery and Direct Lake models, support is limited (row-level expressions that fold in DirectQuery; restricted in Direct Lake), which is another reason to push logic upstream.
⚠️ Exam Trap: A calculated column that uses a measure or CALCULATE triggers context transition and can be very expensive on a large fact table, and its result still won't respond to slicers. If the requirement says "percentage of the selected total" or "rank among the filtered products," no calculated column can satisfy it; it must be a measure.
Reflection Question: A Customer table needs a Customer Tier (Gold, Silver, Bronze) based on each customer's lifetime sales, and users must slice by tier. Why is a calculated column acceptable here even though it uses sales data, and what changes if tiers must be based on sales within the user's selected date range?
