CALCULATETABLE DAX Function (Filter) Context Transition
Evaluates a table expression in a context modified by filters.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
| Table |
The table expression to be evaluated. |
|
| Filter | Optional Repeatable |
A boolean (True/False) expression or a table expression that defines a filter. |
Return values
The value is the result of the expression evaluated in a modified filter context.
Remarks
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
Examples
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"
)
| Color |
|---|
| Silver |
| Blue |
| White |
| Red |
| Black |
| Green |
| Grey |
Related articles
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
-
Using RELATED and RELATEDTABLE in DAX
RELATED and its companion function RELATEDTABLE, are two common DAX functions that are required when using a row context with relationships. In this article we describe why and when to use these two functions. » Read more
-
Filter columns, not tables, in DAX
One of the few golden rules in DAX is to always filter columns and never filter tables with CALCULATE. This article explains the rationale behind the rule. » Read more
Related functions
Other related functions are:
Last update: Oct 27, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://docs.microsoft.com/en-us/dax/calculatetable-function-dax