Forum

What is the differe...
 
Share:
Notifications
Clear all

What is the difference between Calculated Columns and Measures in Power BI?

1 Posts
1 Users
0 Reactions
669 Views
Posts: 9
Topic starter
(@Sanjeev)
Joined: 6 months ago

This question helps interviewers assess whether you understand DAX fundamentals, data modeling, and performance optimization.


Calculated Columns in Power BI

Definition:

  • A calculated column is a new column created in a table using DAX.

  • The calculation is performed row by row, and the results are stored in the model.

Example:
If you have a Sales table with Price and Quantity columns, you can create a calculated column:

TotalCost = Sales[Price] * Sales[Quantity]

Characteristics:

  • Stored in the data model (increases model size).

  • Calculated at data refresh or when the model loads.

  • Useful when you need row-level values (e.g., profit per transaction).

Advantages:

  • Can be used in slicers, filters, and row-level operations.

Disadvantages:

  • Increases memory usage because results are stored.

  • Can affect performance in large datasets.


Measures in Power BI

Definition:

  • A measure is a calculation created using DAX that is evaluated on the fly based on filter context.

  • The result is not stored in the model but calculated dynamically.

Example:
For the same Sales table, you can create a measure:

TotalSales = SUM(Sales[Price] * Sales[Quantity])

Characteristics:

  • Calculated at query time, not stored in memory.

  • Highly optimized for performance.

  • Always evaluated based on filters, slicers, or report context.

Advantages:

  • Does not increase model size.

  • More efficient and scalable.

Disadvantages:

  • Cannot be used as a row-level value in a table without aggregation.


Key Differences Between Calculated Columns and Measures

FeatureCalculated ColumnsMeasures
StorageStored in the modelNot stored, calculated dynamically
EvaluationRow by rowDepends on filter/report context
PerformanceCan increase file size, slower in big dataOptimized and efficient
UsageGood for row-level calculationsBest for aggregations & KPIs
ExampleProfit per row transactionTotal Profit across all rows

Example Interview Answer

*"Calculated Columns and Measures are both created using DAX, but they serve different purposes. Calculated Columns are evaluated row by row and stored in the model, which makes them useful when we need values at the row level, such as Profit = Sales – Cost.

Measures, on the other hand, are dynamic calculations evaluated at query time, which makes them highly efficient. For example, creating a Total Sales measure allows aggregation across different dimensions like Region, Year, or Product.

In practice, I use Measures wherever possible because they are lightweight and perform better. I use Calculated Columns only when I need row-level calculations or when a column is required for relationships and slicers."*


Key Takeaway

  • Use Measures for KPIs, aggregations, and dashboards (preferred for performance).

  • Use Calculated Columns only when row-level data is necessary.

  • Knowing when to use each one is a sign of strong Power BI modeling expertise.


Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Share: