top of page
hand-businesswoman-touching-hand-artificial-intelligence-meaning-technology-connection-go-

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.





75 views0 comments
bottom of page