Calculated Columns and Measures
Create custom columns and measures in your data model using DAX formulas.
Columns and Measures in DAX both are core elements in Power BI, Excel Power Pivot, and SSAS Tabular models, but they serve different purposes.
Calculated Columns
A Calculated Column is a column added to an existing table using a DAX formula. It's calculated row-by-row when the data is loaded or refreshed.
Measures
A Measure is a calculation that evaluates based on the filter context of a report visual. It’s computed on the fly when the report is used.
Detailed Comparison
| Feature | Calculated Column | Measure |
|---|---|---|
| When Calculated | At data load or refresh | When the user interacts with the report |
| Evaluation Context | Row context | Filter context |
| Storage | Physically stored in memory | Not stored; computed dynamically |
| Performance | Can increase model size | More efficient for large datasets |
| Usage | Useful for grouping, filtering, and relationships | Used for KPIs, aggregations, and visuals |
| Example Use Case | Tax per row, Full Name from First + Last Name | Total Sales, Average Discount, Profit Margin |
| Syntax Example | FullName = Customers[FirstName] & " " & Customers[LastName] | Total Sales = SUM(Sales[Amount]) |
Key Differences
Calculated Columns work like Excel formulas: they add a new field to your table. You can use them in relationships, slicers, and row-level filters.
Measures are dynamic aggregations: they adjust based on filters and user interaction, and are ideal for KPIs, totals, and ratios.
When to Use Which?
| Scenario | Use |
|---|---|
| Need a new field to filter or slice data | Calculated Column |
| Need to calculate a total, average, or KPI | Measure |
| Creating a relationship between tables | Calculated Column |
| Optimizing report performance | Measure (preferred over many columns) |