Power BI Lesson 33 – Measures vs Columns | Dataplexa

DAX · Lesson 33

Measures vs Calculated Columns

You know the conceptual difference from Lesson 31 — measures respond to filter context, calculated columns are fixed per row. This lesson goes deeper: the performance and storage consequences of choosing one over the other, the specific scenarios where the choice is forced, the patterns that look like they need a calculated column but actually need a measure, and how to diagnose which one is causing a problem when a report behaves unexpectedly.

What Actually Happens at Refresh vs Query Time

The timing difference between measures and calculated columns is not just conceptual — it has real consequences for file size, refresh speed, and report interactivity. Understanding the lifecycle of each type explains why the choice matters beyond "which feels more natural."

Calculated Column lifecycle

Computed at refresh · stored in model · fixed until next refresh

Refresh triggered — Power BI loads data from Power Query into the model.

DAX engine evaluates each row of the table, computing the formula with row context active.

One value stored per row — the result is compressed and stored in the VertiPaq column store. Adds to the .pbix file size.

Report query time — the column value is simply read from storage. No computation happens. It is as fast as reading any other column.

Cost: paid at refresh time and in storage · zero cost at query time

Measure lifecycle

Defined at model build · computed on demand · never stored

Measure is defined — the formula is saved in the model metadata. Nothing is computed. Zero storage used.

User interacts with report — a slicer changes, a visual renders, a filter is applied. Power BI sends a query to the DAX engine.

DAX evaluates within filter context — the measure formula runs against only the rows currently visible after all filters are applied.

Result returned to visual — discarded after display. If the filter changes, the measure recalculates from scratch.

Cost: zero at refresh · paid at query time · zero storage overhead

Storage and Performance Implications

Scenario	Calculated Column impact	Measure impact
10M row fact table, 5 new columns	+50M stored values — significant model size increase, longer refresh, more RAM required	Zero bytes added — 5 measures add nothing to model size
Column used in a slicer	Works — calculated columns appear in the slicer field well and filter correctly	Cannot — measures cannot be placed in a slicer or used as a row/column grouping axis
Value used in a relationship key	Works — a calculated column can be the key column in a relationship	Cannot — relationships require stored column values, not on-demand computations
Aggregation that responds to filters	Cannot — a column's value is fixed; it cannot aggregate differently based on a slicer	Only measures do this — their whole purpose is filter-context-aware aggregation
VertiPaq compression	Low-cardinality columns compress very well — a Status column with 5 distinct values is tiny. High-cardinality columns (like a hash or GUID) compress poorly and bloat the model.	No storage at all — compression is irrelevant for measures

The Complete Decision Framework

Work through these questions in order. The first yes determines the answer.

Decision tree — Measure or Calculated Column?

Does it need to respond to slicers or cross-filter between visuals?

→ Measure

Does it aggregate — SUM, COUNT, AVERAGE, DIVIDE — over a set of rows?

→ Measure

Will it be used in a slicer, as a filter field, or on a chart axis as a grouping dimension?

→ Calc Column

Will it be used as a relationship key (join between two tables)?

→ Calc Column

Does it reference another table using RELATED() — bringing a lookup value into this table's row context?

→ Calc Column

None of the above — it is a per-row label, flag, or derived value that does not need to filter or aggregate.

→ Calc Column

Default: if unsure, start with a measure. You can always move the logic to a calculated column later; the reverse requires rebuilding slicers and relationships.

RELATED() — Pulling Lookup Values into Row Context

RELATED() is a calculated column function — it uses the active row context to traverse a relationship and retrieve a value from the related table. It is the DAX equivalent of VLOOKUP, except it uses the model relationship instead of a key column argument. RELATED() only works in calculated columns (and iterator functions) because it requires row context.

// Model: Orders → (Many-to-One) → Customers
// Orders[CustomerID] relates to Customers[CustomerID]

// Add CustomerName to the Orders table via the relationship
// New calculated column in the Orders table:
CustomerName = RELATED(Customers[CustomerName])
// For each row in Orders, RELATED() traverses the relationship
// to find the matching Customers row and returns its CustomerName

CustomerCity = RELATED(Customers[City])
CustomerTier = RELATED(Customers[Tier])

// RELATED only works with Many-to-One direction (from the many side)
// Orders is the "many" side → RELATED() goes to the "one" side (Customers) ✓
// Customers is the "one" side → RELATED() going to "many" would return
//   a table, not a scalar — use RELATEDTABLE() instead for that direction

// RELATEDTABLE — from the one side to many
// Calculated column in Customers table:
OrderCount = COUNTROWS(RELATEDTABLE(Orders))
// Returns the number of Orders rows that relate to each Customer row

// Common RELATED pattern: calculate margin using cost from a Products table
// Orders calculated column:
Margin = Orders[Revenue] - RELATED(Products[StandardCost])

RELATED() — CustomerName and CustomerTier pulled into Orders table

OrderID	CustomerID	Revenue	CustomerName (RELATED)	CustomerTier (RELATED)
1001	C101	1,200	Alice Brown	Premium
1002	C102	850	Bob Singh	Standard
1003	C103	430	Carol Lee	Premium
1004	C999	95	BLANK	BLANK

Order 1004 has CustomerID C999 which does not exist in Customers → RELATED returns BLANK for both columns

Patterns That Look Like Calculated Columns But Should Be Measures

Three common DAX requirements fool beginners into creating calculated columns when measures are the correct choice. The tell-tale sign is always the same: the value needs to change based on what the user has filtered.

❌ Wrong — Calculated Column

Revenue % of Total as a calculated column. The column divides each row's revenue by the grand total revenue at refresh time. When a slicer filters to UK only, the denominator is still the original grand total — the percentages no longer add up to 100%.

-- Calculated column (WRONG for % of total)
Rev% = DIVIDE(
    Orders[Revenue],
    SUM(Orders[Revenue])  -- fixed at refresh
)

✓ Correct — Measure

A measure recalculates the denominator using ALLSELECTED on every visual render. When a slicer filters to UK, the denominator becomes UK total revenue — the percentages add up to 100% for the filtered set.

-- Measure (CORRECT)
Revenue % of Total =
DIVIDE(
    SUM(Orders[Revenue]),
    CALCULATE(
        SUM(Orders[Revenue]),
        ALLSELECTED(Orders)
    )
)

❌ Wrong — Calculated Column

Rank by Revenue as a calculated column using RANKX. Computed at refresh over all rows. When a user filters to a single product category, the ranks are still the original full-dataset ranks — a product ranked 50th overall still shows 50, not its rank within the category.

-- Calculated column (WRONG for dynamic rank)
Rank = RANKX(
    ALL(Orders),
    Orders[Revenue]
)

✓ Correct — Measure

A RANKX measure recalculates rank within the current filter context. Filter to Electronics and every product's rank is its rank within Electronics only.

-- Measure (CORRECT)
Revenue Rank =
RANKX(
    ALLSELECTED(Products),
    [Total Revenue],,
    DESC
)

❌ Wrong — Calculated Column

Running total as a calculated column. Computed once at refresh in an arbitrary row order. The running total resets if the data is sorted differently, and does not respond to date filters.

-- Calculated column (WRONG for running total)
RunningTotal = CALCULATE(
    SUM(Orders[Revenue]),
    Orders[OrderDate] <= EARLIER(Orders[OrderDate])
)

✓ Correct — Measure

A measure using DATESYTD or a FILTER on the Calendar table gives a running total that respects date filters, year boundaries, and slicer selections.

-- Measure (CORRECT)
YTD Revenue =
CALCULATE(
    SUM(Orders[Revenue]),
    DATESYTD(Calendar[Date])
)

When a Calculated Column Is Genuinely the Right Choice

// These five patterns are legitimate calculated columns

// 1. Category label for slicing — fixed per row, used in a slicer
-- In Orders table:
RevenueTier =
    SWITCH(
        TRUE(),
        [Revenue] >= 1500, "Platinum",
        [Revenue] >= 1000, "Gold",
        [Revenue] >= 500,  "Silver",
        "Bronze"
    )
// Used in: slicer, chart axis, filter panel

// 2. RELATED lookup — brings dimension value into fact table row
-- In Orders table:
CustomerCity    = RELATED(Customers[City])
CustomerTier    = RELATED(Customers[Tier])
ProductCategory = RELATED(Products[Category])
// Needed when: you want to slice Orders by a Customers attribute
// but do not want to add a relationship to a separate table

// 3. Composite key — combining two columns as a relationship key
-- In Orders table (when no single unique key exists):
OrderKey = Orders[CustomerID] & "-" & FORMAT(Orders[OrderDate], "YYYYMMDD")

// 4. Date component for axis sorting
-- In Calendar table:
YearMonthKey = [Year] * 100 + [MonthNumber]  -- sorts correctly as integer
// Used in: sort-by-column setting to sort month names chronologically

// 5. Flag column for filtering — used as a filter, not aggregated
-- In Orders table:
IsLateFlag =
    IF(
        ISBLANK([OrderDate]) || ISBLANK([DueDate]),
        0,
        IF([OrderDate] > [DueDate], 1, 0)
    )
// Used in: filter panel or as a slicer ("Show late orders only")

Teacher's Note: The "% of total" mistake is the most common calculated column error in Power BI, and it is invisible until you filter. The column looks correct in a table visual showing all data — every percentage is calculated correctly and they add up to 100%. The moment a slicer is applied, the denominator does not change but the numerators do, and suddenly the percentages for the filtered set add up to some arbitrary number below 100%. Always build percentage-of-total as a measure with ALLSELECTED. Test it immediately by applying a slicer and confirming the percentages still sum to 100%.

Practice

Practice 1 of 3

To retrieve the value of a column from a related table for each row in the current table — for example, bringing CustomerCity from the Customers table into the Orders table — you use the DAX function ___ inside a calculated column.

Practice 2 of 3

A Revenue % of Total calculated column appears correct when all data is shown, but when a slicer filters to one region the percentages no longer add up to 100%. This is because the column's denominator was fixed at ___ time and cannot respond to the slicer.

Practice 3 of 3

A calculated column stores one value per row in the model and is compressed by the VertiPaq engine. A column with very few distinct values — like a Status column with five possible values — ___ very well and adds little to the model size.

Lesson Quiz

Quiz 1 of 3

You need a field that shows each customer's city on a map visual, pulled from the Customers table into an Orders-based report. Should you use RELATED() in a calculated column, or build a measure?

Calculated column using RELATED(Customers[City]) in the Orders table. Placing geographic fields on a map requires a stored column value — a measure cannot be placed in the Location field well of a map visual. RELATED() requires row context which exists in a calculated column. Measure using RELATED(Customers[City]) — RELATED() works in both measures and calculated columns, and a measure uses less storage than a calculated column on a large Orders table. Neither — just add a direct relationship between the map visual and the Customers table using the CustomerID field. The map will pick up City directly from Customers without any calculated column or measure. Calculated column, but using LOOKUPVALUE() instead of RELATED() — LOOKUPVALUE() does not require a model relationship and is therefore more flexible for geographic lookups in map visuals.

Quiz 2 of 3

A 50 million row fact table has 8 calculated columns added to it over time. The model file has grown to 2.4GB and refreshes take 45 minutes. Which columns are most likely to be contributing disproportionately to the size problem?

Columns with very few distinct values like Status (5 values) or RegionCode (12 values) — VertiPaq compresses these poorly because the column has too few values to build an efficient dictionary Date columns like OrderDate and DeliveryDate — dates are stored as datetime floats and cannot be compressed by VertiPaq, making date calculated columns the largest per-row type High-cardinality columns — a concatenated key like CustomerID & "-" & OrderID where almost every row has a unique value, or a free-text description column. VertiPaq's dictionary compression works best when many rows share the same value; unique values per row compress very poorly and bloat the model. Calculated columns that use RELATED() — traversing a relationship during column computation stores the relationship path in the column metadata, adding significant overhead per row compared to columns computed from the same table

Quiz 3 of 3

A colleague builds a Sales Rank calculated column using RANKX(ALL(Products), Products[Revenue]). They show it in a table visual filtered to Electronics and notice every product shows its rank across all products, not its rank within Electronics. What is the correct fix?

Replace ALL(Products) with ALLSELECTED(Products) inside the calculated column formula — ALLSELECTED respects the current filter context even inside a calculated column Convert the rank from a calculated column to a measure: Revenue Rank = RANKX(ALLSELECTED(Products), [Total Revenue],, DESC). Calculated columns are fixed at refresh and cannot respond to the Electronics filter. Only a measure recalculates rank within the current filter context. Add a visual-level filter to the table visual that filters the rank column to show only values where Category = "Electronics" — this overwrites the global rank with a category-scoped rank without changing the formula Replace RANKX with RANK.EQ which is the filter-context-aware version of RANKX — RANKX always ranks across all rows while RANK.EQ ranks within the current filter context automatically

Next up — Lesson 34 covers the most commonly used DAX functions in depth, including every aggregation function, the text functions used in real reports, the date functions that do not require time intelligence, and the logical patterns every DAX author uses daily.

← Previous Course Index Next →

Power BI Course

Measures vs Calculated Columns

What Actually Happens at Refresh vs Query Time

Storage and Performance Implications

The Complete Decision Framework

RELATED() — Pulling Lookup Values into Row Context

Patterns That Look Like Calculated Columns But Should Be Measures

When a Calculated Column Is Genuinely the Right Choice

Practice

Lesson Quiz