CALCULATETABLE

Updated on

CALCULATETABLE is a DAX function that evaluates a table expression in a context modified by filters.

Syntax

CALCULATETABLE(
   Table,
   [ Filter, ... ]
)
Argument Properties 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

A table of values.

Remarks

  • 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

The following example uses the CALCULATETABLE function to get the sum of Internet sales for 2006. This value is later used to calculate the ratio of Internet sales compared to all sales for the year 2006.

The following formula:

= SUMX(
    CALCULATETABLE(
        'InternetSales_USD',
        'DateTime'[CalendarYear] = 2006
    ),
    [SalesAmount_USD]
)

It results in the following table:

Row Labels Internet SalesAmount_USD CalculateTable 2006 Internet Sales Internet Sales to 2006 ratio
2005 $2,627,031.40 $5,681,440.58 0.46
2006 $5,681,440.58 $5,681,440.58 1.00
2007 $8,705,066.67 $5,681,440.58 1.53
2008 $9,041,288.80 $5,681,440.58 1.59
Grand Total $26,054,827.45 $5,681,440.58 4.59

Other functions related to CALCULATETABLE are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/dax/calculatetable-function-dax

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy