[]
        
(Showing Draft Content)

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.