Power Pivot Principles: The A to Z of DAX Functions – CALCULATETABLE

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions. This week, we look at one of the key functions in DAX: CALCULATETABLE.

Wrong sort – but you know what we mean!!

This function is similar to CALCULATE, but in this case, it evaluates a table expression in a modified filter context.  Its syntax is as follows:

It has several arguments:

• table expression: this is required and is the table expression to be evaluated
• filter1, etc: these are optional Boolean expressions or table expressions that define filters of filter modifier functions.

The table expression used as the first parameter must be a model table or a function that returns a table.  Filters may be:

• Boolean filter expressions
• filter modification functions
• table filter expressions.

The last filter, a table expression filter, applies a table object as a filter.  It could be a reference to a model table, but more likely it's a function that returns a table object.  You can use the FILTER function to apply complex filter conditions, including those that cannot be defined by a Boolean filter expression.

When there are multiple filters, they can be evaluated by using the AND (&&) logical operator, meaning all conditions must be TRUE, or by the OR (||) logical operator, meaning either condition can be TRUE.

In case you were wondering, a Boolean expression filter is an expression that evaluates to either TRUE or FALSE.  Furthermore, there are several rules that they must abide by:

• they can reference columns from a single table
• they cannot reference measures
• they cannot use a nested CALCULATE function.

Beginning with the September 2021 release of Power BI Desktop, the following also apply:

• they cannot use functions that scan or return a table unless they are passed as arguments to aggregation functions
• however, they can contain an aggregation function that returns a scalar value.

It should be noted that:

• when filter expressions are provided, the CALCULATETABLE function modifies the filter context to evaluate the expression.  For each filter expression, there are two possible standard outcomes when the filter expression is not wrapped in the KEEPFILTERS function:
  • if the columns (or tables) aren't in the filter context, then new filters will be added to the filter context to evaluate the expression
  • if the columns (or tables) are already in the filter context, the existing filters will be overwritten by the new filters to evaluate the CALCULATETABLE expression

• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Example

Let’s create a measure that calculates the sales for division 3 in the year 2020:

Using the CALCULATETABLE formula alone will not work, as Power Pivot will return with the error:

As it stands above, the current CALCULATETABLE measure returns with a table looking like this:

Power Pivot can’t aggregate an entire table into a single cell.  We have to specify which expression we want to be evaluated.  We have to use the SUMX function. The SUMX function ‘returns the sum of an expression evaluated for each row in a table’, it requires the following syntax to operate:

With that in mind we can adjust our previous measure:

The resulting PivotTable:

Of course, this result can be achieved using the CALCULATE function:

As it stands the CALCULATETABLE and the CALCULATE functions are quite similar. The key difference is their outputs. We would use the CALCULATETABLE function when we need to use other functions that expect a table as an expression and CALCULATE when we use functions that expect a single value.

Come back next week for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.