Skip to main content

Measure v Calculated Columns

Data, Pivot Table and Charts.

J
Written by Jo Bigg
Updated over 3 months ago

As an example - trying to add the below as a measure to a pie chart.

This will not work as the aggregation method for a measure is not correct for use in this component type.

VAR CreatedDate = Average('Job'[CreatedDate]) 
VAR IssuedDate = Average('Job'[IssuedDate]) 
RETURN DATEDIFF(CreatedDate, IssuedDate, DAY)

Measures and Calculated columns

Creating Measures and Calculated columns in different component types (pivot, data tables and charts) are going to work differently.

When to Use

  • Use Calculated Columns when you need to store a calculated value as part of the data model, and when that value should not change based on slicers or filters (e.g., when creating new flags, categories, or specific calculations that don’t depend on aggregation).

  • Use Measures when you need to create summary calculations, such as totals, averages, counts, or other aggregates that should change dynamically with filters, slicers, or grouping in reports (e.g., calculating the total days difference or the average difference in a Pivot Table).

Measures and Calculated Columns in Data /Pivot Tables

As a Measure

To implement as a measure in DAX, you need to modify the approach slightly.

Measures are evaluated in the context of the entire report, not row by row like calculated columns.

To calculate the difference between CreatedDate and IssuedDate as a measure, you can use SUMX (or another iterator function) to evaluate it over all rows or within a specific context.

Here's how you can do it:

Measure for Total Difference in Days

If you want to calculate the total difference in days between CreatedDate and IssuedDate across all rows, the measure would look like this:

Example: TotalDaysDifference

SUMX( 'Job', DATEDIFF('Job'[CreatedDate], 'Job'[IssuedDate], DAY) )

  • Explanation:

    • SUMX: This function iterates over each row in the Job table.

    • For each row, it calculates the DATEDIFF between CreatedDate and IssuedDate in days.

    • It then sums up all the differences to give the total difference in days.

Measure for Average Difference in Days

If you're looking for the average difference in days between CreatedDate and IssuedDate, you can create a measure like this:

Example: AverageDaysDifference

AVERAGEX( 'Job', DATEDIFF('Job'[CreatedDate], 'Job'[IssuedDate], DAY) )

  • Explanation:

    • AVERAGEX: This function calculates the average of an expression (in this case, the DATEDIFF between CreatedDate and IssuedDate) over all rows in the Job table.

Measure for Min or Max Difference in Days:

To find the minimum or maximum days difference, you can use MINX or MAXX like this:

Max Days Difference

MAXX( 'Job', DATEDIFF('Job'[CreatedDate], 'Job'[IssuedDate], DAY) )

Min Days Difference

MINX( 'Job', DATEDIFF('Job'[CreatedDate], 'Job'[IssuedDate], DAY) )

Explanation of DATEDIFF

  • DATEDIFF('Job'[CreatedDate], 'Job'[IssuedDate], DAY) calculates the difference between CreatedDate and IssuedDate in days.

The key difference between a measure and a calculated column is that measures are calculated dynamically based on the context in which they are evaluated (e.g., based on filters or slicers in the report), while calculated columns are evaluated row by row when the data is loaded or refreshed.

The issue with your calculated column lies in the fact that you're using AVERAGE() on the CreatedDate and IssuedDate columns. AVERAGE() is typically used for numerical values, and since CreatedDate and IssuedDate are likely date columns, it might not work as expected.

If you're trying to calculate the difference between the CreatedDate and IssuedDate for each row, you should avoid using AVERAGE() and instead directly reference the row values for those columns.

Here’s a corrected version of your calculated column:

VAR CreatedDate = 'Job'[CreatedDate] 
VAR IssuedDate = 'Job'[IssuedDate] 
RETURN DATEDIFF(CreatedDate, IssuedDate, DAY)

This will calculate the difference in days between the CreatedDate and IssuedDate for each row in the Job table.

If you're trying to calculate the average difference across all rows, then you'd need to use AVERAGE() in a measure, not a calculated column, like this:

Measure for average day difference

AVERAGE( DATEDIFF('Job'[CreatedDate], 'Job'[IssuedDate], DAY) )

To aggregate the difference between CreatedDate and IssuedDate as a measure, you'd use a combination of DATEDIFF (or simple subtraction of dates) within an aggregation function like AVERAGE, SUM, or COUNTROWS, depending on how you want to aggregate it.

Here’s how to approach this in DAX, depending on your needs

Average Days Difference

If you want to calculate the average number of days between CreatedDate and IssuedDate for all rows in the table, you can create a measure like this:

Example: AverageDaysDifference 

AVERAGE( DATEDIFF('Job'[CreatedDate], 'Job'[IssuedDate], DAY) )

This measure will calculate the average of the DATEDIFF across all rows.

Total Days Difference

If you want to calculate the sum of all day differences (i.e., the total of all differences between CreatedDate and IssuedDate), you can use the SUMX function:

Example: TotalDaysDifference

SUMX( 'Job', DATEDIFF('Job'[CreatedDate], 'Job'[IssuedDate], DAY) )

Here, SUMX iterates over the rows in the Job table, calculates the DATEDIFF for each row, and sums up those differences.

Maximum or Minimum Days Difference

If you want to calculate the maximum or minimum number of days between CreatedDate and IssuedDate, you can use MAXX or MINX:

Max Days Difference

Example: MaxDaysDifference

MAXX( 'Job', DATEDIFF('Job'[CreatedDate], 'Job'[IssuedDate], DAY) )

Min Days Difference

Example: MinDaysDifference

MINX( 'Job', DATEDIFF('Job'[CreatedDate], 'Job'[IssuedDate], DAY) )

Count of Jobs with Positive Difference

If you want to count how many jobs have a positive day difference between CreatedDate and IssuedDate, you can use COUNTROWS with a FILTER:

Example: PositiveDaysCount

COUNTROWS( FILTER( 'Job', DATEDIFF('Job'[CreatedDate], 'Job'[IssuedDate], DAY) > 0 ) )

This measure will count all rows where the CreatedDate is earlier than the IssuedDate (i.e., where the difference is positive).

General Syntax for Date Calculations

The DATEDIFF function works like this:

DATEDIFF(<StartDate>, <EndDate>, <Units>)

Where <Units> can be:

  • DAY for days

  • MONTH for months

  • YEAR for years

  • HOUR, MINUTE, SECOND, etc.

Calculated Column

A Calculated Column is a column that is created in a table using DAX. This column is computed for each row in the table when the data is loaded or refreshed. Once the calculated column is created, it becomes a part of the table just like any other column and is stored in memory.

Key Points

  • Row-by-row evaluation: Calculated columns are evaluated for each row independently at the time of data refresh or loading.

  • Static Calculation: The values in calculated columns don't change dynamically based on slicers, filters, or user interaction; they are static once computed.

  • Stored in the Data Model: The result is physically stored in the data model, meaning that for large datasets, this can consume memory.

  • Context: A calculated column doesn’t change depending on filters applied on a report unless those filters directly impact the row data itself.

Example Use Case

If you have a CreatedDate and IssuedDate in your Job table, and you want to create a column that shows the difference in days for each row, you would use a calculated column:

Example: DaysDifference

DATEDIFF('Job'[CreatedDate], 'Job'[IssuedDate], DAY)

This would create a column DaysDifference where each row stores the calculated difference in days. This column is now a static value for every row in the data model, and it doesn't change with report filters.

Measures

A Measure, on the other hand, is a dynamic calculation that is performed at the time of report generation, based on the context that the report is currently in. Measures are not stored in the table but are evaluated on the fly, based on the filters and slicers that are applied.

Key Points

  • Context-dependent evaluation: Measures are evaluated dynamically based on the filter context (such as slicers, rows, columns, etc.) in the report at the time of viewing.

  • Dynamic Calculation: The value of a measure can change depending on the filters or slicers that are applied in the report.

  • Not Stored: Measures are calculated on the fly, meaning they don't occupy physical space in the data model, which can be more efficient for large datasets.

  • Context: A measure’s result can change depending on the context in which it’s evaluated (e.g., different filters or slicers). This makes it ideal for aggregations or summaries that need to reflect the current view of the report.

Example Use Case

If you want to calculate the average difference between CreatedDate and IssuedDate across all rows but also want this to change when filters are applied, you would use a measure:

Example: AverageDaysDifference

AVERAGEX( 'Job', DATEDIFF('Job'[CreatedDate], 'Job'[IssuedDate], DAY) )

This measure calculates the average difference, and this value will change based on the filter context. For example:

  • If you add a date filter to the report, the result will change dynamically.

  • If you use a Pivot Table and slice by Job Category, the average difference will be calculated for each category based on the filtered data.

Key Differences in Behavior

Calculated Columns vs Measures

Aspect

Calculated Column

Measure

Evaluation Time

Calculated once during data load or refresh

Evaluated dynamically based on filter context at report time

Row-by-Row Calculation

Yes, evaluated for each row

No, evaluated for the entire filtered context

Stored in Model

Yes, it is a part of the table (physical column)

No, it’s a dynamic calculation that exists only in memory

Filter Context

Does not change based on report filters (static)

Changes dynamically based on filters and slicers

Performance Impact

Can impact memory usage for large datasets (stored in the model)

More memory efficient since it’s calculated on demand

Aggregation

Cannot perform aggregations like SUM, AVG, etc. on its own

Ideal for performing aggregations or calculations in Pivot tables

Use Cases

Best for row-level calculations that don’t change with context

Best for summary-level calculations or aggregations that need to reflect context (e.g., totals, averages)

Why Does This Matter in Pivot Tables?

  • Pivot Tables typically use measures because they need to aggregate data dynamically. If you tried to use a calculated column in a Pivot Table, the values would be displayed row by row, and you wouldn't be able to dynamically aggregate them based on slicers or filters.

  • A Measure allows the Pivot Table to adjust to any slicer, filter, or grouping applied to the report, giving it flexibility in showing totals, averages, counts, or other aggregations.

For example:

  • Calculated Column: You might calculate the difference in days for each row, but when used in a Pivot Table, it will show all rows of data, not aggregated totals.

  • Measure: You can create a measure to sum, average, or count the differences, and the measure will dynamically adjust to reflect the filters applied.

When to Use

  • Use Calculated Columns when you need to store a calculated value as part of the data model, and when that value should not change based on slicers or filters (e.g., when creating new flags, categories, or specific calculations that don’t depend on aggregation).

  • Use Measures when you need to create summary calculations, such as totals, averages, counts, or other aggregates that should change dynamically with filters, slicers, or grouping in reports (e.g., calculating the total days difference or the average difference in a Pivot Table).

For Charts

The key to using charts effectively in data modelling is understanding when to use Measures vs Calculated Columns, as they behave differently based on context and the type of aggregation required.

Here's a brief breakdown of each:

Calculated Columns

  • Use Case: When you want to add new columns to your data model based on row-level calculations.

  • When to Use: Ideal for static calculations that do not change with filters or slicers. For instance, if you want a column that shows the difference between CreatedDate and IssuedDate for every row, this should be a calculated column.

  • Key Characteristics:

    • Calculated once during data refresh or loading.

    • Static: Values don’t change with slicers or filters on the report.

    • Stored in the data model, which can take up memory (especially for large datasets).

Example: DaysDifference

DATEDIFF('Job'[CreatedDate], 'Job'[IssuedDate], DAY)

This would calculate the days difference for each row, and the result would not change based on filters in a report.

Measures

  • Use Case: When you need dynamic aggregations or calculations that change depending on the report's context (e.g., slicers or filters).

  • When to Use: Measures are best for summary-level calculations such as totals, averages, or aggregated differences. For example, if you want to calculate the average difference between CreatedDate and IssuedDate across all rows but want it to change based on filters or slicers in the report, you need a measure.

  • Key Characteristics:

    • Calculated dynamically based on the current context (filters, slicers, rows, etc.).

    • Not stored in the data model but computed at report time.

    • Great for pivot tables or charts, where you need to see aggregated results that change interactively.

Example: AverageDaysDifference

AVERAGEX('Job', DATEDIFF('Job'[CreatedDate], 'Job'[IssuedDate], DAY))

This measure will dynamically calculate the average difference, changing based on the context (filters, slicers) applied in the report.

Why Does This Matter for Charts (like Pie Charts)?

Charts, including Pie Charts, work best with Measures because they need to display aggregated values. Calculated columns, being row-by-row values, don't aggregate automatically, making them less useful in charts that require dynamic grouping or summary calculations.

For example, if you tried to use your DaysDifference (calculated column) in a Pie chart, you would likely run into issues because pie charts require aggregated data. You would need a measure that calculates something like the total, average, or count of differences to use in that context.

Example of Correct Use in a Chart

If you want to show the total days difference in a pie chart, you’d need a measure that sums the differences:

Example: TotalDaysDifference

SUMX('Job', DATEDIFF('Job'[CreatedDate], 'Job'[IssuedDate], DAY))

This measure can now be used in a chart, and it will dynamically adjust based on any filters or slicers applied to the report.

In Summary

  • Calculated Columns: Good for static, row-level calculations that do not depend on the context (e.g., adding a new column to the table).

  • Measures: Ideal for aggregated calculations (e.g., total, average) that need to change based on the current filter context, and are necessary for most chart components.

When working with charts, always use Measures for dynamic, context-sensitive aggregations and Calculated Columns for row-level, static values that don't change with filters or slicers.

Related Articles
Forms
SSR
Did this answer your question?