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

• KEEPFILTERS

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

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

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.

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

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

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

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

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.