Downloads
DAXFilter and contextIntermediate

CALCULATETABLE

Evaluates a table expression in a filter context that you change.

Syntax
CALCULATETABLE(<table> [, <filter> [, <filter> [, ...]]])

Returns: A table.

What it does

CALCULATETABLE is CALCULATE for tables. Instead of returning one number, it returns a set of rows after changing the filter context the same way CALCULATE does. Use it when a later step needs a list, such as the customers who bought online, or the sales lines that an audit should review.

Syntax and arguments

Example

An audit team wants every sales line that carries a return amount, to review as a sample.

Return Lines =
CALCULATETABLE (
    Sales,
    Sales[ReturnAmount] > 0
)

The result is a table of all columns of Sales, limited to the lines with a return in the current filter context. On its own in a DAX query, it lists those rows. In a measure, wrap it in COUNTROWS or another aggregator, because a measure must return a single value.

Example: a second use

Which customers bought through the online channel? The list can feed another calculation.

Online Customers =
CALCULATETABLE (
    VALUES ( Sales[CustomerKey] ),
    Channel[ChannelName] = "Online"
)

The Channel filter reaches Sales through the relationship, so the list holds only customers with online sales. VALUES returns each customer key once. Inside a measure, COUNTROWS(CALCULATETABLE(VALUES(Sales[CustomerKey]), Channel[ChannelName] = "Online")) gives the number of online customers.

Things to watch

In business terms

Finance teams use it to build customer lists for a collections or reactivation campaign, sample lines for an audit, or the set of accounts behind an exception report. Paired with COUNTROWS, it answers "how many items meet this condition in this period?"

Related functions

All DAX functions · View as Markdown · DAX fundamentals