2.4 — Measures, Calculated Columns and Calculated Tables
PL-300 Domain 2 · Skill 2.4
What You Need to Know
DAX can create three kinds of objects. Choosing the right one is a frequent exam question.
| Measure | Calculated column | Calculated table | |
|---|---|---|---|
| Calculated | At query time, for each cell of a visual | At refresh, once per row | At refresh |
| Context | Filter context of the visual | Row context of the current row | None (a table expression) |
| Stored in the model | No | Yes, takes memory | Yes |
| Responds to slicers | Yes | No (values are fixed until refresh) | No |
| Use on axes, slicers, rows of a matrix | No | Yes | Yes (its columns) |
| Example | Total Sales = SUM(Sales[Amount]) |
Margin % Band = IF(Sales[Margin%] > 0.3, "High", "Low") |
Date = CALENDAR(...), Ship Date = 'Date' |
When to use each
- Measure: any number that should aggregate and respond to filters: totals, ratios, year-over-year, ranking. Most calculations should be measures.
- Calculated column: a value you need to slice, filter, group or sort by, or a value that must be calculated row by row before aggregation and can't be done earlier in Power Query.
- Calculated table: a date table, a copy of a dimension for a role-playing date (lesson 2.2), a disconnected table for a slicer, or a summary table built with
SUMMARIZECOLUMNS.
Prefer Power Query for columns
A column that only depends on other columns of the same row (Full Name from first and last name) is better added in Power Query or the source:
- It compresses better than a DAX calculated column
- It keeps logic in one place and can fold to the source
- Calculated columns can't reference Power Query steps and are recalculated on every refresh
Use a DAX calculated column when the value needs the model, such as RELATED(Product[Category]) across a relationship, or a lookup in another table.
Ratios belong in measures
A Profit % calculated column, averaged in a visual, gives the average of row percentages, which is wrong. A measure DIVIDE([Total Profit], [Total Sales]) gives the correct ratio at every level. DIVIDE also returns BLANK (or an alternate result) instead of an error when the denominator is zero.
Explicit vs implicit measures
Dragging a numeric column to a visual creates an implicit measure (Sum of Amount). Explicit measures are reusable, can be formatted once, are required for calculation groups (lesson 2.7), and work in Excel's Analyze in Excel.
Key Concept: If it must respond to slicers, write a measure. If you must slice by it, create a column, ideally in Power Query. Never store a ratio in a column and average it.
Exam Traps
- Creating a calculated column for a total that must change with slicers
- Averaging a percentage column instead of dividing two measures
- Using a calculated column for something Power Query can do (bigger model, harder maintenance)
- Using a measure on a slicer or a chart axis (not possible)
- Using
/instead of DIVIDE when the denominator can be zero
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 22 PL-300 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.
Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.
Cancel anytime. Or €199 a year, which saves 17%.
Not sure yet? Try 10 free mock exam questions.
Already Pro? Log in.
Sources
- Calculated columns in Power BI Desktop, Microsoft Learn
- Create measures for data analysis in Power BI Desktop, Microsoft Learn
- Using calculated tables in Power BI Desktop, Microsoft Learn
- DIVIDE function vs divide operator, Microsoft Learn