8. Adding Columns: Power Query Add Column Tab and DAX Calculated Columns
8.12 Calculated Columns vs Measures in Depth
This is the most asked DAX interview question. Understand it well.
Definitions
- Calculated column: a new column in a table. DAX evaluates it once per row, at refresh, and stores the result in the model.
- Measure: a named formula that is not stored. It is evaluated at query time, every time a visual needs it, in the filter context of that visual cell.
Storage and memory. A calculated column is compressed and stored like any column, so it increases file size and RAM. This matters most on large fact tables and high-cardinality results (for example a unique text per row). A measure stores only its formula, so its memory cost is almost zero. It costs CPU when it is evaluated.
Context. Calculated columns work in row context (the current row). Measures work in filter context (the filters from slicers, rows, columns, filters pane and RLS). The same formula can give completely different results in the two places.
The same logic implemented both ways
Goal: show Net Sales (Amount − Discount) by City.
-- Option A: calculated column + implicit/explicit sum
Net Amount = Orders[Amount] - Orders[Discount] -- column, stored per row
Net Sales (A) = SUM(Orders[Net Amount]) -- measure over the column
-- Option B: measure only (iterator), nothing stored
Net Sales (B) = SUMX(Orders, Orders[Amount] - Orders[Discount])
Both return the same number for every city. Option B uses no extra memory. Option A is useful if you also want to filter or bucket by Net Amount per line.
Goal: % of orders delivered within 10 minutes.
-- Column (row level flag) – good for slicing
Within 10 Min = IF(Orders[Delivery Time Mins] <= 10, 1, 0)
-- Measure (ratio) – must be a measure, because a ratio cannot be summed
% Under 10 Mins =
DIVIDE(
CALCULATE(DISTINCTCOUNT(Orders[Order ID]), Orders[Delivery Time Mins] <= 10),
CALCULATE(DISTINCTCOUNT(Orders[Order ID]), Orders[Order Status] = "Delivered")
)
A ratio stored as a column is wrong when it is summed or averaged across cities (the average of percentages ≠ the overall percentage). Ratios, KPIs and time intelligence must be measures.
| Calculated column | Measure | |
|---|---|---|
| Result | A value for each row | One value for the current filter context |
| When calculated | At refresh | At query time (each visual/interaction) |
| Stored? Memory | Yes, uses RAM and file size | No, only the formula |
| Context | Row context (use CALCULATE for context transition) | Filter context |
| Responds to slicers? | No (fixed per row) | Yes |
| Use in slicer, axis, rows/columns, filter, relationship | Yes | No (Values/fields that accept measures; filters on visuals) |
| Typical use | Categories, flags, keys, sort columns, bands | Totals, ratios, KPIs, YTD, ranking |
| Where else possible? | Often better in Power Query | Only in DAX |
Decision rule
Ask: "Do I want to slice/filter/group by this value, or do I want to show/aggregate it?" Slice by → column (preferably in Power Query). Show/aggregate → measure.
He section punha ekda vacha, mitrano. Column vs measure cha farak spasht jhala ki DAX khup sopa hoto.
Ravindra Bagale's Tip
Mitrano, ithe chuk karu naka: the classic mistake is creating a "Profit %" calculated column and then averaging it in a visual, which gives a wrong percentage. Ratios must be measures: DIVIDE([Gross Profit], [Total Sales]). Also remember that a calculated column storing "today" is fixed at refresh, not live. Dhyan rakho!