DAX Filter - CALCULATETABLE function


Advertisements

Description

Evaluates a table expression in a context modified by the given filters.

Syntax

CALCULATETABLE (<expression>, [<filter1>], [<filter2>] …)

Parameters

Sr.No. Term & Definition
1

expression

The table expression to be evaluated.

2

filter1, filter2 …

A Boolean expression or a table expression that defines a filter.

Return Value

A table of values.

Remarks

The expression used as the first parameter must be a function that returns a table.

If Boolean expressions are used as arguments, the following restrictions apply −

  • The expression cannot reference a calculated field

  • The expression cannot use a nested CALCULATE function.

  • The expression cannot use any function that scans a table or returns a table, including aggregation functions.

However, a Boolean expression can use any function that looks up a single value, or that calculates a scalar value.

CALCULATETABLE function changes the context in which the data is filtered, and evaluates the expression in the new context that you specify. For each column used in a filter argument, any existing filters on that column are removed, and the filter used in the filter argument is applied instead.

CALCULATETABLE function is a synonym for the RELATEDTABLE function.

Example

= SUMX ( 
   CALCULATETABLE (East_Sales, 
   FILTER (East_Sales, East_Sales[Product]=[Product])), 
   East_Sales[Sales Amount]
) 
dax_functions_filter.htm

Useful Video Courses


Video

Mastering DAX and Data Models in Power BI Desktop

53 Lectures 5.5 hours

Abhay Gadiya

Video

Mastering DAX Studio

Featured

24 Lectures 2 hours

Randy Minder

Video

DAX / Power BI - Customer and Sales Analysis Deep Dive

26 Lectures 4.5 hours

Randy Minder

Advertisements