Data Modeling

## Two different objects that both use DAX A Power BI model (the semantic model, called a *dataset* before the Fabric-era rename) holds its tables in VertiPaq, an in-memory columnar engine. DAX can add two very different things to that model, and confusing them is the single most common beginner error in Power BI modelling. A **calculated column** adds a new column to an existing table. It is evaluated **row by row, once, at data-refresh time**, and the result is compressed and stored alongside every other column in the table. It has *row context*: inside the formula, a bare column reference means "this row's value", and `RELATED` can reach across a relationship to fetch the matching row on the one side. A **measure** adds a calculation to the model that has **no stored value at all**. It is evaluated **at query time**, once per cell the visual asks for, against the set of filters that cell is under. A measure has no row context of its own — it must aggregate (`SUM`, `COUNTROWS`, `SUMX`, …), which is why a measure body that references a bare column without an aggregator is an error. ## What that difference implies **Where you can use it.** A slicer, a chart axis, a matrix row header, a legend, a `Group by` field, and the key of a relationship all require *a column* — a real set of values that exists before any query runs. A measure cannot fill any of those roles; it only ever produces a value inside a cell. So if the requirement is "let users slice by price band", the price band must be a calculated column (or, better, a column created upstream). **Cost.** A calculated column consumes model memory and lengthens refresh. The cost is driven by *cardinality*: a low-cardinality column such as a three-value price band compresses to almost nothing, while a per-row calculated key or a per-row timestamp calculation can be one of the largest columns in the model. Calculated columns are also computed after the table is loaded, so they can compress worse than an equivalent column imported from the source. Measures cost nothing at rest; they cost CPU at query time. **Correctness at totals.** This is the deciding argument for ratios. If `Margin %` is stored per row, a matrix total can only aggregate those stored numbers — summing percentages is meaningless, and averaging them weights a one-dollar order the same as a million-dollar order. As a measure, `DIVIDE(SUM(Sales[Profit]), SUM(Sales[Revenue]))` re-evaluates at every grain, including the total row, and produces the correct weighted figure everywhere. ## The upstream option A third answer beats both in many interviews: do it in **Power Query** or in the source view. A column produced upstream is materialised the same way as any imported column, often compresses better, keeps DAX in the model simple, and (when the source is a database) can fold into SQL. Reserve calculated columns for logic that genuinely depends on the model — for example a value that needs `RELATED` across a relationship that only exists in Power BI, or a column derived from another calculated column. ## Worked contrast Given a `Sales` table with `Revenue` and `Profit`, and a `Product` table joined one-to-many to it: - `Product[Price Band] = IF(Product[ListPrice] > 100, "High", "Low")` — a calculated column. It is an attribute of a product, it has three possible values, it belongs on a slicer. - `Sales[Product Category] = RELATED(Product[Category])` — a calculated column that *works* but is usually a waste: the relationship already lets any visual group `Sales` measures by `Product[Category]`. Copying the attribute onto the fact table just spends memory and defeats the star schema. - `Total Profit = SUM(Sales[Profit])` and `Margin % = DIVIDE([Total Profit], SUM(Sales[Revenue]))` — measures. They must respond to whatever the user filtered. ## What interviewers listen for A strong answer names three axes — *when it is evaluated*, *whether it is stored*, and *where it can be used* — and then states the ratio-at-totals failure as the concrete consequence. A weaker answer describes them as two syntaxes for the same thing, or reaches for a calculated column first because it feels more like a spreadsheet. Mentioning that most calculated columns should have been created upstream is what separates a modeller from someone who only writes formulas.