- Getting Started
- Administration Guide
-
User Guide
- An Introduction to Wyn Enterprise
- Document Portal for End Users
- Data Governance and Modeling
- Working with Resources
- Working with Reports
- Working with Dashboards
- View and Manage Documents
- Understanding Wyn Analytical Expressions
- Section 508 Compliance
- Subscribe to RSS Feed for Wyn Builds Site
- Developer Guide
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
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')
Remarks
This function is not used alone, but as an intermediate function that can be used to change the result set that has performed other calculations.
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 filter expressions, they can be evaluated using the AND (&&) logical operator. |
Example for Boolean filter expression
CALCULATE (
SUM ( Sales[Sales Amount] ),
'Sales'[OrderDateKey] > 1 && 'Sales'[OrderDateKey] <100
)
Example for Table filter expression
CALCULATE (
SUM ( Sales[Sales Amount] ),
Filter('Sales','Sales'[OrderDateKey] > 1 && 'Sales'[OrderDateKey] <100)
)
The difference between a Boolean filter and a table filter.
Type | Description |
---|---|
Boolean filter expressions | This filter is added as a where clause after the joined table. |
Table filter expressions | This filter is internally joined as a table. |
Filter Modification Functions
Filter modifier functions allow you to add more than just filters. They also give you additional control when modifying the filter context.
The "REMOVEFILTERS" function can only remove unnecessary dimensions. An example is as follows.
When calculating the Sales Amount, ignore the latitude CustomerName. The expression is as below:
CALCULATE (
SUM ( Sales[Sales Amount] ),
REMOVEFILTERS('Sale'[CustomerName])
)
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])
)
This example will calculate the Sales Amount total when the three filter criteria are met.
Remarks
All filter expressions act on the inner table of the first parameter. This means that the filter expression in the CALCULATE function is a pre-filter.
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 filter expressions, they can be evaluated using the AND (&&) logical operator. |
Return Value
The table of values.
Example:
SUMX(
CALCULATETABLE(
'InternetSales',
'DateTime'[CalendarYear] = 2021
),
[SalesAmount]
)
The above example uses the CALCULATETABLE function to get the sum of Internet sales for the year 2021. This value can be used to calculate the ratio of Internet sales compared to all sales for the year 2021.
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)
The above formula will return a table containing only rows where the OrderDateKey column is greater than 1 and less than 100.
REMOVEFILTERS
Description
The REMOVEFILTERS function clears filters from the specified tables or columns.
Syntax
REMOVEFILTERS([<TableNameOrColumName>] [,<ColumnName> [,<ColumnName> [ , ...] ] ] )
Parameters
Parameter | Description | Attributes |
---|---|---|
TableNameOrColumnName | The name of an existing table or column that you want to clear filters on. | Optional |
ColumnName | A column in the same base table. | Optional Repeatable |
Remarks
REMOVEFILTERS clear filters from the specified tables or columns. It can be used only as a CALCULATE/CALCULATETABLE modifier and has no return value.
Example
EVALUATE
CALCULATETABLE (
CALCULATETABLE (
SUMMARIZE ( 'Product', 'Product'[Category], 'Product'[Brand], 'Product'[Color] ),
REMOVEFILTERS ( 'Product'[Color] )
),
Product[Brand] = "Litware",
Product[Color] = "Red"
)
The above formula will remove the filter on the product color, and return the values where Product[Brand] = "Litware" only.
Limitations
Only simple filters can be removed.
Example:
Calculate(
Calculate(
SumX(Sales,Sales[Price] * Sales[Quantity]),
RemoveFilters('Product'[Color])
),
(Product[Brand] = "Litware") && (Product[Color] = "Red")
)
In the above expression, Filter (Product[Brand] = "Litware") && (Product[Color] = "Red") is a complex filter, so the 'Product'[Color] can't be removed.
You can split it into two simple filters and apply the filters as shown in the below expression.
Calculate(
Calculate(
SumX(Sales,Sales[Price] * Sales[Quantity]),
RemoveFilters('Product'[Color])
),
Product[Brand] = "Litware",
Product[Color] = "Red"
)
KEEPFILTERS
Description
The KEEPFILTERS function modifies how filters are applied while evaluating a CALCULATE or CALCULATETABLE function.
Syntax
KEEPFILTERS(Expressions)
Parameters
Parameter | Description |
---|---|
Expression | Any expression. |
Return Value
A table of values.
Example
EVALUATE ROW(
"$$ in TX"
, CALCULATE('FactInternetSales'[Internet Total Sales]
, 'DimGeography'[State Province Code]="TX"
)
, "$$ in TX and OH"
, CALCULATE('FactInternetSales'[Internet Total Sales]
, 'DimGeography'[State Province Code]="TX"
|| 'DimGeography'[State Province Code]="OH"
)
, "$$ in TX and NV"
, CALCULATE('FactInternetSales'[Internet Total Sales]
, 'DimGeography'[State Province Code]="TX"
|| 'DimGeography'[State Province Code]="NV"
)
, "$$ in TX and OH ??"
, CALCULATE(
CALCULATE('FactInternetSales'[Internet Total Sales]
,'DimGeography'[State Province Code]="TX"
|| 'DimGeography'[State Province Code]="OH"
)
, 'DimGeography'[State Province Code]="TX"
|| 'DimGeography'[State Province Code]="NV"
)
, "$$ in TX !!"
, CALCULATE(
CALCULATE('FactInternetSales'[Internet Total Sales]
, KEEPFILTERS('DimGeography'[State Province Code]="TX"
|| 'DimGeography'[State Province Code]="OH"
)
)
, 'DimGeography'[State Province Code]="TX"
|| 'DimGeography'[State Province Code]="NV"
)
)
The above example represents some common scenarios that demonstrate the use of the KEEPFILTERS function as part of a CALCULATE or CALCULATETABLE formula.
The first three expressions will get the simple data and can be used for comparisons:
Internet Sales for the state of Texas.
Internet Sales for the states of Texas and Ohio (both states combined).
Internet Sales for the state of Texas and the province of Nevada (both regions combined).
The fourth expression calculates Internet Sales for Texas and Ohio, with the filter for Texas and Nevada applied.
The next expression calculates Internet Sales for Texas and Ohio but uses KEEPFILTERS; the filter for Texas and Nevada is part of the prior context.
For [$$ in TX and OH ??]: this formula will return the value for sales in Texas and Ohio, although the outer CALCULATE expression includes a filter for Texas and Nevada. The explanation for this is as follows: the default behavior of CALCULATE overrides the outer filters in 'DimGeography'[State Province Code] and substitutes its own filter arguments, because the filters apply to the same column.
Remarks
You can use KEEPFILTERS within the context of CALCULATE and CALCULATETABLE functions, to override the standard behavior of these functions.