Calculated columns and measures are two fundamental concepts in Power BI’s DAX language that serve distinct but complementary roles in data modeling and analysis.
Both allow you to create new fields based on existing data, but they differ in how and when the calculations are performed, how they impact model size, and their usability in reports.
Calculated columns operate at the row level and are computed during data refresh or load time. They effectively add new columns to a table where each row’s value is individually calculated and stored in the model.
As stored data, calculated columns can be used as any regular column—meaning they can act as slicers or filters in reports and even form relationships with other tables.
However, because their values are static until the next refresh and they physically increase the data model size, overusing calculated columns can lead to slower refresh times and larger file sizes.
Measures, on the other hand, are dynamic calculations performed at query time based on the current filter context from visuals, slicers, and user interactions.
They don’t consume storage space in the data model since they exist only as formulas evaluated on demand.
Measures are designed primarily for aggregations such as sums, averages, counts, ratios, or more complex calculations that need to adjust dynamically in response to report context changes.
Unlike calculated columns, measures cannot be used as slicers or filters directly but are essential for building interactive and efficient reports.
Comparison of Calculated Columns and Measures
Best Practice Guidelines
1. Use calculated columns when you need persistent row-level data for slicing, grouping, or relationships (e.g., categorizing customers by region).
2. Use measures for aggregations that depend on report filters or slicers (e.g., total sales, average revenue per region).
3. Limit calculated columns to minimize model size and optimize refresh times.
4. Prefer measures for calculations involving filters, user-driven interactivity, or time intelligence functionality.
5. Test performance and optimize DAX formulas to balance speed and functionality.
Calculated Column Example
Creating a "Profit" column in a sales table:
Profit = Sales[Revenue] - Sales[Cost]
This creates a static value per row used for grouping and filtering.
Measure Example:
Calculating total profit dynamically based on filters:
Total Profit = SUM(Sales[Profit])
This measure recalculates as filters change, e.g., by year or product category.
Class Sessions
Sales Campaign
We have a sales campaign on our promoted courses and products. You can purchase 1 products at a discounted price up to 15% discount.