Consider the following SQL syntax:
It corresponds to this DAX query using FILTER:
You can also use a DAX query using CALCULATETABLE:
In case of a simple SQL query like the initial query, there are no semantic differences between the two corresponding DAX options. However, we see a different behavior when other expressions or a more complex filter condition are present.
Consider a double filter like this one:
You have two options using FILTER. The simplest one is applying the same logical expression to a single FILTER:
With this approach, the logical conditions (color red and list price greater than 1000) are applied to every row in Product, even if some optimization might happen internally to reduce the number of comparisons made.
This is the corresponding solution using CALCULATETABLE:
The filter arguments in CALCULATETABLE are always put in a logical AND condition. Even if the results produced by FILTER and CALCULATETABLE are identical, the performance might be different. The CALCULATETABLE could be faster if the number of values returned by each logical condition on single columns is relatively small (more details in the Understanding DAX Query Plans white paper). In fact, every filter argument in CALCULATETABLE corresponds to an internal FILTER on a single column. The previous CALCULATETABLE syntax is internally rewritten as:
Do not assume that CALCULATETABLE is good and FILTER is bad. Every CALCULATETABLE internally incorporates one or several FILTER, and performance is generally determined by the granularity of each FILTER (even if the DAX engine might perform further optimization).
In the case of an OR condition, you should write the entire logical condition as a single condition even in CALCULATETABLE, including a FILTER condition inside. Consider the following SQL statement:
The corresponding DAX syntax using FILTER is:
The corresponding CALCULATETABLE version requires an explicit FILTER argument in order to specify a logical condition that includes two columns of the same table (in this case, no automatic FILTER rewriting is possible).
At this point, you might think that FILTER is simpler to use. However, you should always consider the CALCULATETABLE option, because of the different behavior when you have nested calculations.
For example, consider the following SQL query that returns red products with a total sales amount greater than 100,000 and an average sales amount greater than 3,000 in the calendar year 2006.
The equivalent DAX expression using only FILTER is shorter than the SQL expression. However, you would still specify the Calendar Year filter in two calculations (sum and average):
Using CALCULATETABLE, the filter arguments (color and calendar year) are applied to the entire expression specified in the first argument. For this reason, the two CALCULATE expressions in the FILTER of the following DAX query do not have to include the filter on calendar year, because it is “inherited” from the outer CALCULATETABLE filters.
Using CALCULATETABLE you propagate a filter to all the expressions embedded in the first argument. This results in shorter syntax simplifying the query, even if it requires particular attention – outer CALCULATETABLE and CALCULATE statements manipulate the filter context and affect any inner expression.