CALCULATETABLE
Evaluates a table expression in a filter context that you change.
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
- table: any table expression, such as a table name,
VALUES(...)orFILTER(...). Required. - filter (optional, repeat as needed): the same kinds of filters CALCULATE accepts: a Boolean condition on one column, a table filter, or a modifier.
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
- A table cannot be shown as a single value. Wrap the function in
COUNTROWS,SUMXor another aggregator inside a measure. - It follows the same filter rules as
CALCULATE: Boolean filters replace existing filters on the same column. CALCULATETABLE(Sales, ...)returns every sales row. To get a list of distinct values, useVALUESorDISTINCTon one column.- Keep table filters narrow.
FILTERover a whole fact table is slow. VALUEScan add a blank row for keys with no matching dimension row.DISTINCTdoes not.
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?"