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.

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

- Payment is instant and you will receive a tax invoice straight away.
- Your satisfaction is paramount: we offer a no-quibble refund guarantee.

- Demo: CALCULATE Overcomes Limitations of ALL (02:18)
- The Syntax of CALCULATE (05:30)
- Filters in CALCULATE (06:30)
- Demo: Using Filters with CALCULATE (08:54)
- Demo: Complex Conditions (19:07)
- CALCULATE Inside Row Context (26:58)
- Demo: Row Context (29:32)
- VALUES in Filter (41:32)
- Demo: VALUES (43:18)
- Summary (47:04)

## Comments

crimiss · 28 June 2013

good day,

after learning dax and implementing tabular models in powerpivot for the past 11 months i have hit a wall regarding a particular problem. i am trying to conduct ABC Analysis (Pareto Analysis) in powerpivot using a dax measure instead of the calculated columns approach. i've created a virtual table but have discovered that there is no row context within the virtual table. in the article on sqlbi.com titled "best practices using summarize and addcolumns" its suggested that a row context can be generated by using the calculate function. any ideas/hints on how to approach this with the SUMX function from within a virtual table? Also, is there a define table syntax similar to the define measure present when using dax queries? Your assistance on this would be greatly appreciated.

Marco Russo · 29 June 2013

The dynamic ABC is possible but can be very slow. You cannot create local virtual tables in a DAX query. The solution is creating a physical table with the three values (A, B, C) and for each one performing a calculation that sums only products of a specific class. The problem is that you should create the limit of each class in the query and this would be repeated too many times, for this reason this could be really really slow.