DP-600: create and manage efficient calculated columns

João Barros 17 de August de 2026 4 min read

In this hands-on DP-600 lesson I will teach you how to create and manage Calculated Columns in Power BI/Fabric semantic models. This skill is important for the exam because calculated columns affect modeling, data consistency and performance; in practice they allow you to meet business needs without touching the sources.

What you need to know

A calculated column is a column added to the model table that is evaluated row-by-row using DAX. Unlike measures, calculated columns store values in the model (persisted) and are useful for categorization, composite keys, flags and transformations that will be used in filters or relationships.

Simple example: add a YearMonth column based on a Date column:

YearMonth = FORMAT(Sales[Date], "YYYY-MM")

Important to distinguish calculated columns from measures and from columns generated at the source (Power Query):

Calculated columns (DAX) are evaluated in the model engine and stored, increasing model size.
Measures (DAX) are calculated dynamically according to the filter context and not stored.
Transformations in Power Query are applied before loading the data and reduce storage and calculation costs in the model.

How it works / In practice

Steps to create and manage calculated columns efficiently:

Determine if it is really necessary: ask whether the value needs to be persisted for relationships, indexes or row-level filters. If it is only an aggregation or dependent on context, use a measure.
Prefer Power Query when possible: if the transformation can be done in extract/transform (for example, parsing, type change, static joins), do it in Power Query to reduce model size.
Write efficient DAX: use lightweight row-context functions and avoid iterators when not needed. Functions like RELATED, LEFT/RIGHT, FORMAT, YEAR are less costly than iterators like SUMX when misused.
Avoid data duplication: do not duplicate existing columns; instead create semantic views or relationships that leverage columns already present.
Test and validate: check null values, types and performance with larger samples before applying to the full table.

Practical example: create a Customer Segment column from a simplified RFM.

Recency = DATEDIFF(Customer[LastPurchaseDate], TODAY(), DAY)
Frequency = Customer[PurchaseCount]
Monetary = Customer[LifetimeValue]

CustomerSegment =
SWITCH(
  TRUE(),
  Customer[Recency] <= 30 && Customer[Monetary] > 1000, "Top",
  Customer[Recency] <= 90 && Customer[Frequency] > 5, "Active",
  "Other"
)

Practical notes about the example:

CALCULATE Recency/Monetary/Frequency as calculated columns if those values do not exist at the source or cannot be computed in Power Query.
The use of TODAY() makes the column dependent on the processing time — this means it needs periodic recalculation (for example, scheduled refresh).
If values change frequently, consider migrating to measures or recalculating at source to avoid storage and maintenance cost.

Common mistakes

Using calculated columns where measures should be used: creating columns for calculations that depend on context (e.g., dynamic percentages) increases the model and yields incorrect results.
Ignoring impact on model size: stored calculated columns can inflate the .pbix file; use Power Query when possible.
Inefficient DAX functions: excessive use of iterators (SUMX, FILTER) in calculated columns for simple operations can degrade performance and delay refresh processing.

How to practice

Practice with sample datasets (for example, Adventure Works or the Power BI samples) and create scenarios where you need to transform data in modeling. Use Power BI Desktop or Fabric semantic models to experiment with calculated columns and see the impact on size and performance.

To prepare for the exam, use the official and free Microsoft Practice Assessment and the official Study Guide (both free). These resources help you verify the measured areas and identify where to practice more; they do not contain real exam questions — they are meant for skill assessment.

In summary

Calculated columns are persisted in the model and useful for categories, keys and row-level flags.
Prefer Power Query for transformations whenever possible to reduce size and computational load.
Choose between calculated column and measure based on persistence need and filter context.
Test performance and impact on model size; avoid unnecessary DAX iterations.