Difference Between Measures vs Calculated Columns in Power BI?
In Power BI, measures and calculated columns are both used to perform calculations, but they serve different purposes and have distinct characteristics. Both are used to perform data modeling and can be further used for data analysis. Measures are used for aggregations and calculations based on the data in your model. They are typically used in conjunction with numeric columns to perform calculations like sums, averages, counts, etc. Calculated columns are used to create new columns in a table based on a formula or expression. These columns become a permanent part of the data model and are calculated at the time of data refresh or model processing. Calculated columns are static and do not respond to changes in the context of a visualization. The values in a calculated column are fixed once the column is created and are not affected by changes in filters or visualizations.
You can create measures and calculated columns by choosing the highlighted options when you right-click on a dataset in Power BI Desktop.
Table of Contents
What is Measures in Power BI?
In Power BI, measures are a type of calculation that you create using the DAX (Data Analysis Expressions) language for common data analysis. DAX is a formula language designed for creating custom calculations in Power BI, Excel, and other Microsoft BI tools.
Measures are used to perform calculations on your data, such as aggregations, calculations based on conditions, or any other type of data manipulation. Measures are calculated at runtime based on the context of the data being displayed in your reports or visualizations and hence it allows speedy and dynamic on-demand data exploration.
Some of the key features associated with measures in Power BI include:
- Dynamic and fast calculations with the option of flexibility and responsiveness
- Supports data aggregations and can be used in data visualization/reporting
- Ability to perform calculations with context filters
- Performing complex calculations with reference to multiple tables
In Power BI Desktop, you can create measures in multiple ways, such as Report View, Data View, and Model View. You can also use the Quick Measure option to use ready-made measures available in Power BI Desktop for quick measure creation. Power BI provides an option to organize your measures into folders, which can help you manage your Power BI model more effectively. You can also delete measures once successfully created in Power BI. Measures are calculated on the fly when you interact with visuals in Power BI, so they always reflect the most current data.
Example of measure in Power BI:
Total Valuation of Top 100 companies = SUMX(FILTER(Top_1000_Companies_Dataset, Top_1000_Companies_Dataset[GrowjoRanking] < 101), Top_1000_Companies_Dataset[valuation])
You will also notice that all the measures will have an icon populated against the measures in the data table.
What is Calculated Columns in Power BI?
A calculated column is a column that you add to a table in your data model, and its values are calculated based on a formula that you define. These formulas can involve various operations, functions, and references to other columns in the same table. Like measures, you can create calculated columns by using Data Analysis Expressions (DAX) in Report View, Data View, and Model View in Power BI Desktop.
When you create calculated columns, they appear in the Fields like existing data fields in a table i.e. a physical attribute in the table. You can rename the calculated columns and apply formatting such as defining data type, precision, or any other properties and use it for your data visualization or reporting requirements.
When you create a calculated column, it performs calculations across all the rows in the table. However, you can apply context-based filters to your dataset. These calculated columns are recalculated every time your underlying dataset is changed or refreshed.
Some of the key features associated with calculated columns in Power BI include:
- Part of the Data model and hence can be used for establishing relationships between multiple tables
- Apply to all the records in a data table in Power BI
- The values remain static unless the underlying data is refreshed
Example of a calculated column in Power BI:
Benchmark Category = IF(OR(CONTAINSSTRING(‘Mutual Fund Benchmark Monitor R'[Benchmark Name],”Nifty”), CONTAINSSTRING(‘Mutual Fund Benchmark Monitor R'[Benchmark Name],”NIFTY”)),”NIFTY”,”SENSEX”)
Note: Microsoft keeps updating some of the features mentioned above as part of their monthly release updates for Power BI. It is recommended that you refer to the latest Microsoft documentation for any new features or changes to measures and calculated columns.
Comparative Table
There are several similarities and differences between measures and calculated columns in Power BI. The key differences between these two are captured in this section.
| Category | Measures | Calculated Columns |
|---|---|---|
| Purpose | • Measures are used for creating aggregations, summarizations, or calculations based on the values in the columns of your data model. • Measures are typically used in visualizations, providing dynamic and context-aware results based on user interactions in Power BI. | • Calculated columns, on the other hand, are used to create new columns in a table by applying a formula to each row in Power BI. • These columns become a part of the underlying table and can be used for filtering and sorting, but they are not suitable for creating aggregations or dynamic calculations. |
| Context | • Measures are evaluated within the context of a visual or a combination of visuals. •They dynamically respond to filters, slicers, and other context changes in your report, allowing for flexible and interactive data analysis in Power BI. | • Calculated columns are static and are computed when the column is created in Power BI. • They do not respond to changes in the report context or user interactions. The values are precomputed and stored in the table. |
| Calculation | Measures often involve aggregations like SUM, AVERAGE, COUNT, etc. They perform calculations on aggregated data at runtime, providing dynamic results based on the context of the visualization | • Calculated columns typically involve row-level operations. Each row’s value is calculated based on the formula applied to that specific row. • Aggregations on calculated columns are generally performed later when you use them in a visualization or a DAX expression. |
| Memory and Performance | • Measures are generally more memory-efficient because they are calculated on the fly, based on the current context. • They can provide better performance, especially when dealing with large datasets. | • Calculated columns consume additional memory as their values are stored in the table. • Adding many calculated columns with complex formulas can impact the performance and increase the size of the data model. |
| Storage | • Measures, are calculated on the fly and do not consume storage space | • Calculated columns are stored in the underlying data model in Power BI |
| Display | • Measures appear only in the view but not in the data view or model view in Power BI | • Calculated columns appear in the data view as well as the model view in Power BI |
| Reusability | • Measures are reusable and can be referenced across multiple data visualizations and reports | • Calculated columns are restricted to a particular table and hence are not very reusable compared to measures |
| quick measures | • Power BI has specific built-in measures that you can access for either creating new measures or using for your visualization requirements through the quick measure feature | • There are no such features available for calculated columns in Power BI Desktop |
Similarities
Despite a lot of differences between measures, and calculated columns, there are a lot of similarities between these two that can help you make informed decisions to make appropriate choices.
Some of the key similarities are highlighted below:
- DAX (Data Analysis Expression) formula language:
Both measures and calculated columns use the Data Analysis Expressions (DAX) language for defining calculations in the formula bar in Power BI Desktop. DAX is a formula language specifically designed for use with Power BI, Excel, and other Microsoft BI tools.
- Syntax:
The syntax for writing formulas is similar between measures and calculated columns. You use similar functions and operators to create calculations for both in Power BI Desktop.
- Data Refresh:
Both measures and calculated columns are recalculated during data refresh or when there is a change in the underlying data. This ensures that your calculations stay up to date with the latest data in your dataset in Power BI.
- Data Aggregation
Measures and calculated columns can both perform aggregations on data. You can use functions like SUM, AVERAGE, COUNT, etc., to aggregate values based on your requirements.
- Context:
Both measures and calculated columns can be affected by the context in which they are placed. The context is determined by the rows and columns in the visual or table where the measure or calculated column is used.
- Reference to existing columns:
Both measures and calculated columns can reference existing columns in a table or data model in Power BI. This allows you to create calculations based on the reference columns in Power BI Desktop datasets.
In summary, use calculated columns when you need a static, row-level calculation stored in the data model, and use measures when you need dynamic, context-dependent calculations that don’t contribute to the data model size. The choice between the two depends on the specific requirements of your analysis and reporting needs.
Recommended Articles
Guide to Measures vs. Calculated columns inPower BI Differences. Here we learn Difference Between Measures and Calculated columns in Power BI and Comparative Table. You can learn more from the following articles –
Leave a Reply