A calculated column is a column added to a table in a BI data model — typically a Power BI dataset or a tabular Analysis Services model — whose value is computed by a formula expression evaluated row by row at data load time and stored in the model’s in-memory data structure. Unlike a measure, which is computed dynamically at query time, a calculated column has a fixed value for each row, stored in memory alongside the physical columns. The distinction matters for both performance and correctness: calculated columns are appropriate for row-level attributes that will be used as filters, slicers, or grouping fields in visuals — a calculated “Fiscal Year” column on the Date dimension, a calculated “Account Type” category on the Account dimension. They are inappropriate for calculated analytics — ratios, running totals, period comparisons — that depend on the current filter context, which must be implemented as measures.
How It Works
In Power BI and Analysis Services Tabular, calculated columns are defined in DAX. At each data refresh, the formula is evaluated for every row in the table and the result is stored in the model’s VertiPaq columnar store. The stored values are compressed and encoded alongside the physical columns. A calculated column that derives a simple category label — “Expense” if the account type is “Cost,” “Revenue” if the account type is “Income” — adds a small, highly compressible column to the model. A calculated column that performs a complex lookup across tables adds a column whose values are less compressible and whose computation at refresh time adds to the model’s processing duration.
Design Decisions and Trade-offs
The key design decision is choosing between a calculated column and an equivalent transformation at the data preparation layer — in the ETL/ELT pipeline, in the warehouse table, or in a Power Query step. The preference for finance BI models is to push computed attributes to the data preparation layer rather than calculating them in the model: a “Fiscal Quarter” derived from the calendar date belongs in the Date dimension table populated by the ETL, not as a DAX calculated column on every row of the Date dimension in the Power BI model. This preference reduces the complexity of the BI model, keeps business logic in the governed data layer, and reduces model refresh time by eliminating the row-by-row DAX evaluation at refresh.
Common Implementation Errors
The specific calculated column error that most consistently degrades Power BI model performance is using a calculated column with a RELATED() function to bring in a value from a related table — implementing a lookup that should have been a dimension attribute materialised in the ETL. A calculated column like Entity Region = RELATED(DimEntity[Region]) on every row of a large fact table stores the Region value redundantly on each fact row, inflating model memory and slowing refresh. The correct pattern is to include Region as a column in the DimEntity dimension table (materialised in the ETL) and use it there — not to pull it into the fact table via RELATED. Every RELATED() calculated column on a large table is a candidate for elimination through ETL-layer materialisation.
How Loop Wise Solutions Designs for This
We conduct a calculated column audit on every Power BI model we inherit or review — identifying RELATED() calculated columns on fact tables, duplicate derivations that exist in both the ETL and the model, and complex DAX calculated columns that belong in the data preparation layer. Each identified column is evaluated for migration to the ETL layer, reducing model size and refresh time.
