PL-300: optimize models with calculated columns and calculated tables

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

This lesson focuses on the Model the data skill of the PL-300 exam: creating and managing calculated columns and calculated tables using DAX. It is important for the exam because it assesses the ability to transform data in the model and, in practice, helps balance readability, reusability, and report performance.

What you need to know

Calculated columns are columns added to a table in the model with a DAX expression that is evaluated per row. Calculated tables are whole tables defined by a DAX expression (e.g., SUMMARIZE, FILTER, DISTINCT). Both are persisted in the model (consume memory) and are recalculated when the model is processed again.

Simple example: in a Sales table with OrderDate and Quantity, a calculated column Year = YEAR(Sales[OrderDate]) returns the year of each sale. A calculated table Years = DISTINCT(SELECTCOLUMNS(Sales, "Year", YEAR(Sales[OrderDate]))) creates a table with the distinct years.

How it works

Key decisions when choosing between calculated columns, calculated tables, and measures:

Calculated column: use when you need the value per row and want to filter/group by that column in visuals or relationships. Example: categorizing products by price range.
Calculated table: use to create supporting tables (e.g., derived dimension) or to materialize intermediate results that will be reused. Example: creating a date dimension derived from multiple sources.
Measure (not persisted): preferable for dynamic aggregated calculations because it is evaluated in the visual context and does not increase the model’s cardinality.

Performance and memory: calculated columns and calculated tables are stored in the Vertipaq storage. Each additional column increases memory; high-cardinality columns (many unique values) are more costly. Calculated tables duplicate data if they reproduce information already present in other tables.

In practice

Step-by-step to create a calculated column (Power BI Desktop):

1. Abrir Power BI Desktop e carregar os dados.
2. Ir ao separador Data ou Model.
3. Selecionar a tabela Sales.
4. No menu Modelling, clicar em "New column".
5. Escrever a expressão: Year = YEAR(Sales[OrderDate])
6. Confirmar. A coluna aparece e pode ser usada em visuais ou relações.

Example of a calculated table to create a weeks dimension:

Weeks =
ADDCOLUMNS(
    CALENDAR(MIN(Sales[OrderDate]), MAX(Sales[OrderDate])),
    "Year", YEAR([Date]),
    "WeekNumber", WEEKNUM([Date], 2)
)

Best practices for implementation:

Avoid calculated columns for values that are purely aggregated — use measures.
If a column is derived from others and only serves for display (for example, a formatted label), consider creating a measure if it does not need to be filterable in slicers or relationships.
When you need to create dimensions (e.g., calendar), create them as well-designed calculated tables and, if possible, use functions like CALENDAR or CALENDARAUTO for performance and compression.
Document calculated columns/tables with clear names and, if necessary, a description column to facilitate maintenance.

Common mistakes

Creating calculated columns when the logic should be a measure: this increases memory and can harm compression, besides not responding correctly to filter context in visuals.
Ignoring cardinality: adding text columns with many unique values (e.g., repeated transaction ID) makes the model heavy. Consider normalizing or converting to integer keys when possible.
Duplicating information with calculated tables: creating a calculated table that replicates an existing one causes wasted memory and possible data divergence when refreshing the model.

How to practice

Practice using exercises with real data or Power BI sample data (Retail, AdventureWorks). For official PL-300 preparation: use the Microsoft official and free Practice Assessment and consult the Microsoft official study guide (both free). These resources help validate that you cover the skills measured without resorting to dumps.

In summary

Calculated columns and tables persist in the model and affect memory; measures are evaluated in context and do not occupy additional space.
Use calculated columns when you need per-row values and for grouping/filtering; use calculated tables for dimensions or reusable results.
Avoid high cardinality and data duplication; prefer measures when the calculation is a dynamic aggregation.
Practice with official resources: the Microsoft Practice Assessment and study guide are essential to prepare for the PL-300.