Calculated Columns and Measures in Power BI in simple terms.

Computations in DAX at Report Level of Power BI can be done using the following:

1. Calculated Column

2. Measure

If new calculations are required from existing columns of a table, we can use either one, depending on the requirement.

How is Calculated Column computed in Power BI?

On the right hand side of Power BI Desktop, tables which are present in a particular Data Model are displayed. The window to write the formula for a calculated column under a particular table can be opened up by right clicking the table and selecting ‘New column’, on the table under which the computation is required.

Features of Calculated Columns are:

• Value computed in a calculated column is displayed as a new column as a part of the table.

• Value is computed in a Row context (Row by Row) by default and is aggregated afterwards.

• Values in a calculated column are computed either at defining the column (i.e. executing the formula) or during data refresh and is stored in the model.

• Calculated columns take up space in RAM. This is the reason they are not preferred when there is an option to do the same calculations with a measure.

• Calculated columns are useful when the computed value is to be used for slicing, dicing, grouping and sorting in a report.

• Calculated columns are useful when there are complex calculations involved and the value needs to be computed beforehand and used in a report later.

• As Calculated columns work on a Row by Row basis, they do not have access to Filter context.

• Calculated columns return one value per row and rely on Row context only.

How is a Measure computed in Power BI?

The window to write a formula for measure in a Power BI Desktop can be opened up by Right clicking on the table under which calculation is required and selecting ‘New Measure’.

Features of a Measure are:

• Measures are computed on the fly and its value depends upon user selection.

• The only way to look at the value of a computed measure in a Power BI Desktop is to place it on a visual.

• Measures are not useful when slicing and dicing is required as they work on aggregate level under evaluation context, by default.

• Measures have access to Filter Context. They work on Levels of Granularity.

• Measures are dynamic. Their value changes depending on the filters applied.

• Measures don’t consume RAM, but it uses CPU. If calculations are big, it takes time.

• Measures can work on a Row context by adding an ‘X’ to aggregate functions, for e.g. SUMX, MINX,MAXX. These are known as iterator functions.

Important Points about Calculated Columns and Measures:

• Same formula cannot be used for computing Calculated Columns and Measures because they work under different Evaluation Contexts.

• Calculated columns are used when computation is to be done which is bound to Row context.

• Calculated columns are used when the result is to be used in slicer visuals in a report.

• Measures are usually defined when calculating ratios, percentages, aggregates, etc. in a filter context.