In Power BI, both Calculated Columns and Measures are created using DAX, but they serve different purposes and behave differently inside the data model.
Calculated Columns
A calculated column performs a row-by-row calculation and stores the result inside the data model.
Key Characteristics
- Calculated at the time of data refresh
- Stored physically in the model
- Increases model size
- Works at row level
- Can be used in slicers, filters, rows, and columns
Example
Profit = Sales[Revenue] - Sales[Cost]
Here, the calculation is performed for every row in the Sales table and saved in the model.
When to Use Calculated Columns
- When you need a new field for categorization
- When you want to create relationships based on the new column
- When the value does not need to change dynamically based on visuals
Measures
A measure performs dynamic calculations based on filter context. It is not stored in the model but calculated at query time.
Key Characteristics
- Calculated when used in a visual
- Not stored physically in the model
- Does not increase model size significantly
- Works at aggregation level
- Changes based on filters, slicers, and visuals
Example
Total Profit = SUM(Sales[Revenue]) - SUM(Sales[Cost])
This calculation changes depending on the filters applied in the report.
When to Use Measures
- When you need dynamic totals, averages, or KPIs
- When calculations depend on filters or slicers
- For dashboards and summary-level reporting
Main Differences
Calculated Columns are static and row-based, while Measures are dynamic and context-based.
Calculated Columns increase model size because values are stored, whereas Measures calculate results only when needed.
Calculated Columns are mainly used for data modeling, while Measures are primarily used for reporting and analysis.
Conclusion
Use Calculated Columns when you need row-level data stored in the model. Use Measures when you need dynamic calculations that respond to filters and user interactions. Understanding the difference is essential for building efficient and optimized Power BI reports.