CALCULATETABLE DAX Function in Power BI
The CALCULATETABLE DAX function evaluates a table expression in a modified filter context and returns a table of values. Calculated tables are created by using DAX not by Power Query.
Calculated tables have a cost: They increase the model storage size, and they can prolong the data refresh time. The reason is because calculated tables recalculate when they have formula dependencies to refresh tables.
DAX Syntax CALCULATETABLE(expression [, filter1 [, filter2 [, …]]])
The function has the following parameters:
- expression: The table expression to be evaluated.
- filter1, filter2,…: (Optional) Boolean expressions or table expressions that defines filters, or filter modifier functions.
Note: 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.
Order of Evaluation The order of evaluation of the parameters of a function is usually the same as the order of the parameter: the first parameter is evaluated, then the second, then the third, and so on. This is always the case for most of the DAX functions, but not for CALCULATE and CALCULATETABLE. In these functions, the first parameter is evaluated only after all the others have been evaluated.
Example: Let’s we have following data in the table named Sheet1. In the “Country” column we can see, there are multiple country values in the table. Let’s create a Calculated table by clicking on “New table”.
The following dax code creates a calculated table which has the data from the Sheet1 table, and it filters that by Country column and contains only India and Australia.
DAX
The output of the above dax function is shown below:
