Computations in DAX at Report Level of Power BI can be done using the following:
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.