This is a common Power BI (DAX) interview and practice question. Below is the full answer with a clear, step-by-step explanation.

Question

Problem A retail company has created a Power BI report using the following tables: FactSales DimProduct DimCustomer DimDate A junior developer has created several calculated columns to perform business calculations such as: Total Sales Total Profit Profit Margin Average Order Value The report works correctly, but the dataset size has increased significantly and report performance has become slower. As the Power BI developer, answer the following questions: What is the difference between a Calculated Column and a Measure? When is a Calculated Column evaluated? When is a Measure evaluated? Which one consumes storage in the semantic model? Which one responds dynamically to slicers and filters? Give two examples where a Calculated Column is appropriate. Give two examples where a Measure is appropriate. Why are Measures generally preferred for business calculations?

Answer & Explanation

Below is the complete answer you should be able to give in an interview. Read it, understand the why‑and‑how, and you’ll be ready to explain each point clearly.

---

**1. Difference between a Calculated Column and a Measure**
- **Calculated Column** – The DAX expression is run once for every row in a table when the model is processed (or when the data is refreshed). The result is stored as a new column in the table, just like any other column that comes from the source.
- **Measure** – The DAX expression is not stored. It is a formula that is executed only when a visual, a card, or any other query asks for its value. The result is calculated on the fly, using the current filter context.

**2. When is a Calculated Column evaluated?**
It is evaluated during model processing or data refresh. At that moment Power BI reads each row, applies the DAX, writes the result into the column, and then the value stays unchanged until the next refresh.

**3. When is a Measure evaluated?**
A measure is evaluated at query time, i.e., every time the user interacts with the report. The engine re‑calculates the measure using the filter context that comes from slicers, page filters, visual level filters, row/column context of a matrix, etc.

**4. Which one consumes storage in the semantic model?**
Calculated columns consume storage because the result for every row is physically kept in the model. Measures do not store results; they only store the DAX definition, so they add virtually no storage cost.

**5. Which one responds dynamically to slicers and filters?**
Measures respond dynamically. Because they are evaluated in the current filter context, a change in a slicer or filter instantly changes the measure’s result. Calculated columns are static after refresh; they do not re‑evaluate when a user filters the report.

**6. Two examples where a Calculated Column is appropriate**
- **Row‑level categorisation** – e.g., `ProfitCategory = IF([Profit] > 10000, "High", IF([Profit] > 0, "Medium", "Low"))`. The category is needed for grouping, sorting, or as a legend and does not change with filters.
- **Key or surrogate column for relationships** – e.g., `FiscalYear = YEAR([OrderDate])` or a concatenated key like `CustomerProductKey = [CustomerID] & "-" & [ProductID]` that is used to build relationships or to create a sort order.

**7. Two examples where a Measure is appropriate**
- **Total Sales** – `Total Sales = SUM(FactSales[SalesAmount])`. The total must aggregate differently depending on the selected period, product, or customer.
- **Year‑to‑Date (YTD) Sales** – `YTD Sales = TOTALYTD([Total Sales], DimDate[Date])`. The YTD value changes as the user selects different years or applies other filters.

**8. Why are Measures generally preferred for business calculations?**

1. **Memory efficiency** – Measures do not store a value per row, so they keep the model size small, which is critical for large fact tables.
2. **Dynamic behaviour** – Because they are evaluated in the current filter context, a single measure can serve every visual, slicer, or drill‑through scenario without having to create multiple static columns.
3. **Re‑usability** – One measure can be referenced by many visuals, calculated columns, or other measures, reducing duplication and maintenance effort.
4. **Performance at scale** – The VertiPaq engine can aggregate measures very efficiently using columnar storage and compression. Calculated columns, on the other hand, increase the amount of data that must be loaded and compressed, slowing down refresh and query times.
5. **Simpler model** – Keeping business logic in measures keeps the data model tidy; columns are reserved for attributes that are needed for relationships, slicers, or row‑level security.

---

**How to arrive at these answers in an exam or interview**

1. **Recall the definition** – Think “when is the value created?” If it’s stored per row → calculated column; if it’s computed on demand → measure.
2. **Link evaluation time to the lifecycle** – Refresh = column, query = measure.
3. **Connect storage to the fact that a column adds a physical field** – every extra column = extra memory.
4. **Remember filter context** – only measures see the context that slicers build.
5. **Pick realistic scenarios** – any situation where you need a static attribute (category, key, sort order) → column; any situation where you need an aggregation that must change with user interaction → measure.
6. **Explain the preference** – focus on memory, dynamism, re‑usability, and performance.

If you can walk through each point with this reasoning, you’ll demonstrate a solid grasp of the core Power BI concept and why moving the junior developer’s calculations from columns to measures will improve both model size and report speed.