Project Botticelli

Status message

See the new version of this article on Tecflix.com! Why? Read our announcement.

DAX: The CALCULATE Function Purchase this course

19 December 2012 · 2 comments · 16718 views

The Most Important Function in Data Analysis Expressions

DAX: CALCULATE Function with ALL and FILTER

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.

Purchase A Full Access Subscription

Subscription Best Value

$480/year

Access all content for 1 year.
  Purchase on Tecflix

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

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.