19 December 2012 · 2 comments · 9102 views

The Most Important Function in Data Analysis Expressions


CALCULATE is the single most important function in Data Analysis Expression (DAX) because it allows you to manipulate ﬁlter 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 ﬁlter context, and it converts an existing row context into an equivalent ﬁlter context. CALCULATE is important, because it overcomes the limitations of using FILTER and ALL, when they are used to manipulate a ﬁlter context on their own. For example, it is not possible to remove a ﬁlter over a single column in a table, without aﬀecting ﬁlters 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 ﬁlter on some columns of a table without touching ﬁlters on other columns of the same table. You could not achieve that by just using SUMX and ALL! You can control whether a ﬁlter should add to, or replace, the existing ﬁlters 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 ﬁlter context. This is important for manipulating calculations when you deﬁne 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 ﬁlter context is empty when you deﬁne a calculated column, and you can manipulate it by transforming the row context into a ﬁlter context. Because the ﬁlter context automatically propagates to the related tables, you can deﬁne a calculation in a table that automatically ﬁlters 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 deﬁned 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 ﬁlter 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.


Buy Full Access Membership


