CALCULATE is the single most important function in Data Analysis Expression (DAX) because it allows you to manipulate filter context, which is necessary for building real-world calculations. Let Marco Russo, mentor and book author from sqlbi.com, explain it to you in this 47-minute video.

The CALCULATE function performs two fundamental operations: it directly manipulates the filter context, and it converts an existing row context into an equivalent filter context. CALCULATE is important, because it overcomes the limitations of using FILTER and ALL, when they are used to manipulate a filter context on their own. For example, it is not possible to remove a filter over a single column in a table, without affecting filters in other columns, by using ALL on its own. In the demo, you will see that in order to compute a percentage of a total you might need to selectively remove a filter on some columns of a table without touching filters on other columns of the same table. You could not achieve that by just using SUMX and ALL! You can control whether a filter should add to, or replace, the existing filters at a column granularity level by using CALCULATE with the ALL and VALUES functions, on a column.

The second operation performed by CALCULATE is the transformation of a row context into the correspondent filter context. This is important for manipulating calculations when you define a calculated column, or when you specify an expression, inside an iteration function such as SUMX or FILTER. In several practical examples of expressions in a row context, shown in the demo, you will learn that the filter context is empty when you define a calculated column, and you can manipulate it by transforming the row context into a filter context. Because the filter context automatically propagates to the related tables, you can define a calculation in a table that automatically filters rows in any related tables within the CALCULATE scope. It is useful to know that the presence of CALCULATE can be implicit in an expression: whenever a measure is evaluated, CALCULATE is called implicitly to evaluate the expression defined in the measure. For this reason, every measure used in a calculated column, or in an expression within an iteration function, will execute the transformation of the row context into a filter context.

By watching this 47-minute video, which includes 5 full-length, high-resolution demos, you will be able to use the most important function of DAX. Ideally, you should study this video after you have watched the remaining modules of this course, especially the preceding tutorial about DAX Evaluation Context.

Log in or purchase access below to the premium version of this content.