Creating Calculated Columns vs Measures in Power BI

The choice between calculated columns and measures in Power BI comes down to one question: should this value change when someone filters the report? Microsoft's documentation describes the difference from both sides. Calculated columns add a value to every row and, when materialised, are derived at refresh time (Microsoft Learn: calculated columns). The results of measures change as people interact with the report (Microsoft Learn: measures). Get the answer to that question right and most other decisions follow.

The calculations

Here are four common calculations on a table of daily bike rentals. The first two belong in columns; the running total and rank are written both ways to show the difference.

-- Calculated columns: one value per row, stored at refresh
Temp Band = SWITCH ( TRUE (), Days[temp] * 41 < 10, "<10C", Days[temp] * 41 < 20, "10-20C", Days[temp] * 41 < 30, "20-30C", ">30C" )
Is Weekend = Days[weekday] IN { 0, 6 }
Running Total (column) = CALCULATE ( SUM ( Days[cnt] ), FILTER ( Days, Days[dteday] <= EARLIER ( Days[dteday] ) && YEAR ( Days[dteday] ) = YEAR ( EARLIER ( Days[dteday] ) ) ) )
Rank (column) = RANKX ( FILTER ( Days, YEAR ( Days[dteday] ) = YEAR ( EARLIER ( Days[dteday] ) ) ), Days[cnt] )

-- Measures: recalculated for whatever the report is showing
Rentals = SUM ( Days[cnt] )
Running Total = CALCULATE ( [Rentals], FILTER ( ALLSELECTED ( Days[dteday] ), Days[dteday] <= MAX ( Days[dteday] ) ) )
Rank = RANKX ( ALLSELECTED ( Days[dteday] ), [Rentals] )
Casual Share = DIVIDE ( SUM ( Days[casual] ), [Rentals] )

To show what each choice does, the logic is reproduced in pandas on the UCI Bike Sharing daily data for 2012 (UCI: Bike Sharing). Columns are computed once on the whole table; measures are recomputed on the filtered rows, as Power BI would.

import pandas as pd

# A pandas reproduction of Power BI behaviour on the UCI bike-sharing daily data (not run in Power BI).
# Calculated columns are computed once per row at refresh; measures are recomputed for whatever is filtered.
day = pd.read_csv("day.csv", parse_dates=["dteday"])

# "Calculated columns", computed on the full table at refresh
day["Temp Band"] = pd.cut(day.temp * 41, [0, 10, 20, 30, 41], labels=["<10C", "10-20C", "20-30C", ">30C"])
day["Is Weekend"] = day.weekday.isin([0, 6])
year_rows = day[day.dteday.dt.year == 2012].copy()
year_rows["Running Total (column)"] = year_rows.cnt.cumsum()
year_rows["Rank (column)"] = year_rows.cnt.rank(ascending=False, method="min").astype(int)

print("P1 columns are right for slicing and grouping: 2012 days per temperature band")
print("   " + " | ".join(f"{b} {n}" for b, n in year_rows["Temp Band"].value_counts().sort_index().items()))

# A report filter: weekends only. Columns keep their refresh-time values; measures recompute.
weekend = year_rows[year_rows["Is Weekend"]].copy()
weekend["Running Total (measure)"] = weekend.cnt.cumsum()
weekend["Rank (measure)"] = weekend.cnt.rank(ascending=False, method="min").astype(int)
last = weekend.iloc[-1]
top = weekend.sort_values("cnt", ascending=False).iloc[19]
print("\nP2 with the report filtered to weekends in 2012")
print(f"   last weekend day {last.dteday:%d %b}: running total column {last['Running Total (column)']:,} | measure {last['Running Total (measure)']:,}")
print(f"   20th busiest weekend day {top.dteday:%d %b} ({top.cnt:,}): rank column {top['Rank (column)']} | rank measure {top['Rank (measure)']}")

print("\nP3 a ratio needs a measure: casual riders' share of rentals, weekends vs weekdays (2012)")
for label, rows in (("weekends", weekend), ("weekdays", year_rows[~year_rows["Is Weekend"]])):
    print(f"   {label:<9} measure {rows.casual.sum() / rows.cnt.sum():.1%}")

P1 columns are right for slicing and grouping: 2012 days per temperature band
   <10C 18 | 10-20C 156 | 20-30C 156 | >30C 36

P2 with the report filtered to weekends in 2012
   last weekend day 30 Dec: running total column 2,046,847 | measure 565,017
   20th busiest weekend day 16 Sep (7,333): rank column 72 | rank measure 20

P3 a ratio needs a measure: casual riders' share of rentals, weekends vs weekdays (2012)
   weekends  measure 31.0%
   weekdays  measure 13.3%

Case 1: banding and flags belong in columns

A temperature band or a weekend flag is a property of each day that you want on a slicer, an axis or a legend. Measures cannot go on slicers or axes, so these must be columns. The reproduction splits 2012 into 18 days below 10 °C, 156 in each of the middle bands and 36 above 30 °C, a grouping a report can slice by. Where possible, create such columns in Power Query with the rest of the data preparation.

Case 2: running totals must respond to filters

Filter the report to weekends only. The running-total column still shows the value it was given at refresh, 2,046,847 rentals by the last weekend of the year, which is the total for all days, weekends and weekdays. The measure recalculates over the visible weekend days only and shows 565,017. A user reading the column would be badly misled, and nothing on the page would warn them.

Case 3: ranks go wrong quietly

On the weekends-only page, 16 September was the 20th busiest day. The measure ranks it 20th. The rank column, computed against every day of the year at refresh, says 72nd. Both numbers look plausible, which is what makes static ranks dangerous: they are wrong whenever the visible set differs from the set they were computed on.

Case 4: ratios need measures

The share of casual riders was 31.0% on weekends and 13.3% on weekdays in 2012. A measure that divides summed casual rentals by summed total rentals gives the right share for any filter. A per-row share column averaged in a visual would weight every day equally regardless of size; that ratio trap is covered in depth in the DAX guide.

The decision test

Ask, in order:

1. Will it be used to slice, filter, group or join? Then a column, ideally from Power Query.
2. Should it change when the report is filtered? Then a measure.
3. Is it an aggregate or a ratio? Then a measure.
4. Is it expensive and needed for every row in many visuals? Consider a column, but check what filtering will do to it first.

Auditing a model you inherited

Most Power BI developers inherit models rather than build them. A quick audit catches the costly cases. List every calculated column and ask of each: is it used on a slicer, axis, legend or relationship? If not, it is probably a measure in disguise. Pay particular attention to any column whose name contains words such as total, running, rank, share, percent, average or growth, because those almost always describe aggregates. Then open a report page, apply a filter such as a single year or a single region, and compare a suspicious column against an equivalent measure in a table visual. If the two disagree under the filter, the column is wrong for that use. Rewriting a few such columns as measures often makes a model smaller, faster to refresh and, most importantly, correct in the views users actually look at.

A newer nuance: Direct Lake

Microsoft's documentation now notes that in Direct Lake on OneLake semantic models, preview support for calculated columns evaluates certain columns at query time without materialising them, and such columns cannot be used in relationships. The principle above still holds: decide by whether the value should follow the report's filters, then check what your storage mode supports.

Common mistakes

• Running totals, ranks and year-over-year changes built as columns.
• Ratios built as row-level columns and then averaged.
• Measures where a slicer is needed, which simply cannot work.
• Many wide columns that only feed a single visual, adding refresh time and model size.

For a broader set of interview-style questions on these choices, see DAX interview questions.

Modelling and DAX decisions like these are taught in the Power BI course.

Sources

• Using calculated columns in Power BI Desktop (Microsoft Learn) — Microsoft
• Measures in Power BI Desktop (Microsoft Learn) — Microsoft
• Bike Sharing Dataset (UCI Machine Learning Repository) — UCI Machine Learning Repository