CALCULATETABLE DAX Function (Filter) Context Transition
Evaluates a table expression in a context modified by filters.
The table expression to be evaluated.
A boolean (True/False) expression or a table expression that defines a filter.
The value is the result of the expression evaluated in a modified filter context.
Every filter argument can be either a filter removal (such as ALL, ALLEXCEPT, ALLNOBLANKROW), a filter restore (ALLSELECTED), or a table expression returning a list of values for one or more columns or for an entire expanded table.
When a filter argument has the form of a predicate with a single column reference, the expression is embedded into a FILTER expression that filters all the values of the referenced column. For example, the predicate shown in the first expression is internally converted in the second expression.
CALCULATETABLE ( <table_expression>, table[column] = 10 ) CALCULATETABLE ( <table_expression>, FILTER ( ALL ( table[column] ), table[column] = 10 ) )
A filter argument overrides the existing corresponding filters over the same column(s), unless it is embedded within KEEPFILTERS.
CALCULATETABLE follow the same steps of CALCULATE to evaluate its result.
» 1 related function
CALCULATETABLE is identical to CALCULATE, except for the result: it returns a table instead of a scalar value.
-- Returns the colors of Proseware branded products EVALUATE CALCULATETABLE ( VALUES ( 'Product'[Color] ), 'Product'[Brand] = "Proseware" )
Learn more about CALCULATETABLE in the following articles:
FILTER vs CALCULATETABLE: optimization using cardinality estimation
A common best practice is to use CALCULATETABLE instead of FILTER for performance reasons. This article explores the reasons why and explains when FILTER might be better than CALCULATETABLE. » Read more
Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT
This article provides a complete explanation of the behavior of the ALLxxx functions in DAX. When used as filters in CALCULATE, ALLxxx functions might display unexpected behaviors. » Read more
Expanded tables in DAX
Expanded tables are the core of DAX; understanding how they work is of paramount importance. This article provides a theoretical foundation of what expanded tables are, along with fundamental concepts useful when reading DAX code. » Read more
From SQL to DAX: Filtering Data
The WHERE condition of an SQL statement has two counterparts in DAX: FILTER and CALCULATETABLE. In this article we explore the differences between them, providing a few best practices in their use. » Read more
Understanding context transition in DAX
Context transition is one of the most obscure topics for DAX newbies. In this article we introduce context transition, its effects, and how to leverage it rather than be scared of it. » Read more
Other related functions are:
Last update: Jun 10, 2023 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://docs.microsoft.com/en-us/dax/calculatetable-function-dax