Filter Functions

The filter functions return a specific data type, find values in related tables, and filter by related values. The Lookup function works by using the tables and relationships between them. Filter functions help you to manipulate data context for creating dynamic calculations.

Following is the list of filter functions supported in Wyn Enterprise:

• ALL
• CALCULATE
• CALCULATETABLE
• FILTER
• REMOVEFILTERS

ALL

---

Description

The ALL function returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table.

Syntax

ALL([<table>|<column>[,<column>[,<column>[,…]]]]) 

Parameters

Parameter	Description
table	The table that you want to clear filters on.
column	The column that you want to clear filters on.

Return Value

The table or column with filters removed.

Example

ALL('Sales')

CALCULATE

---

Description

The CALCULATE function evaluates an expression in a modified filter context.

Syntax

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

Parameters

Parameter	Description
expression	The expression to be evaluated.
filter1, filter2, ... (optional)	Boolean expressions or table expressions that define filters, or filter modifier functions. Filters can be boolean filter expressions, table filter expressions, and filter modification functions. When there are multiple filters expressions, they can be evaluated using the AND (&&) logical operator.

Return Value

The value is the result of the expression.

Example

CALCULATE (
    SUMX ('Sales', Sales[Sales Amount] ),
    'Sales'[OrderKey] > 20 && 'Sales'[OrderKey] <1010,
    Filter('Sales','Sales'[OrderDateKey] > 1 && 'Sales'[OrderDateKey] <100),
    REMOVEFILTERS('Sale'[CustomerName])
)

CALCULATETABLE

---

Description

The CALCULATETABLE function evaluates a table expression in a modified filter context.

Syntax

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

Parameters

Parameter	Description
expression	The expression to be evaluated.
filter1, filter2, ... (optional)	Boolean expressions or table expressions that define filters, or filter modifier functions. Filters can be boolean filter expressions, table filter expressions, and filter modification functions. When there are multiple filters expressions, they can be evaluated using the AND (&&) logical operator.

Return Value

The table of values.

FILTER

---

Description

The FILTER function returns a table that represents a subset of another table or expression.

Syntax

FILTER(<table>,<filter>)

Parameters

Parameter	Description
table	The table to be filtered. The table can also be an expression that results in a table.
filter	A boolean expression that is to be evaluated for each row of the table. For example, [Amount] > 0, [Region] = "France", etc.

Return Value

A table containing only the filtered rows.

Example

Filter('Sales','Sales'[OrderDateKey] > 1 && 'Sales'[OrderDateKey] <100)

REMOVEFILTERS

---

Description

The REMOVEFILTERS function clears filters from the specified tables or columns.

Syntax

REMOVEFILTERS(<column>)

Parameters

Parameter	Description
column	The column that you want to clear filters on.

Return Value

N/A.

Example

CALCULATE (
SUMX ('Sales', Sales[Sales Amount] ),
REMOVEFILTERS('Sale'[CustomerName])
)