Launch Deal · 50% off 1st month ·

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.

See plans

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.