Filter

Returns the tuples of a set that meet the criteria of a search condition.

Syntax

FILTER ( set, search_condition )

Notes

This function returns the subset of tuples in set for which the value of the search condition is TRUE. The order of tuples in the returned set is the same as in the input set.

Example

Example 1

The following unfiltered query returns profit for all level-0 products:

SELECT
  { [Profit] }
ON COLUMNS,
 [Product].levels(0).members
ON ROWS
FROM Sample.Basic

This query returns the grid:

To filter the above results to only show negative Profit, use the Filter function, passing it the original set and a search condition. Filter will only return the set of members for which the search condition is true (for which Profit is less than zero).

SELECT
  { Profit }
ON COLUMNS,
 Filter( [Product].levels(0).members, Profit < 0)
ON ROWS
FROM Sample.Basic

The resulting query returns only the products with negative profit:

Example 2

The search expression in Example 1 compared a value expression (Profit) with a value. You can also filter using a member attribute as the search condition. For example, you can use the Filter function to only select members whose Caffeinated attribute is TRUE.

SELECT
  { [Profit] }
ON COLUMNS,
 Filter( [Product].levels(0).members, Product.CurrentMember.[Caffeinated])
ON ROWS
FROM Sample.Basic

This query returns profit for the members that are caffeinated:

To understand the search condition, Product.CurrentMember.[Caffeinated], it may be helpful to read it right to left: Filter is searching for presense of the Caffeinated property on the current member, for each member in the input set, which happens to be from the Product dimension (The CurrentMember function requires the dimension name as its argument).

Filter is an iterative function, meaning that at every member or tuple in the set being evaluated, the member being operated upon is the "current member," until Filter has looped through the entire input set and evaluted the search condition for each tuple. So to see how the previous query results were generated, it would be useful to see first which members actually have the Caffeinated attribute set to true. The following unfiltered query uses a calculated member to reveal which of the level-0 product members is caffeinated. The IIF function returns a value of 1 for each member whose Caffeinated attribute is set to TRUE, and returns a value of 0 otherwise.

WITH MEMBER Measures.IsCaffeinated 
AS 'IIF(Product.CurrentMember.[Caffeinated], 1, 0)'
SELECT
  { IsCaffeinated }
ON COLUMNS,
 [Product].levels(0).members
ON ROWS
FROM Sample.Basic

This query returns the grid:

Looking at the results for the second query, you can begin to see that the search condition is evaluated for each tuple in the input set, and that only the tuples meeting the search condition are returned.

Example 3

Example 2 introduced the CurrentMember function. Even when CurrentMember is not explicitly called, Filter operates in the context of "the current member" while it iterates through a set. Filter and other iterative functions are processed in a nested context.

By default, Filter operates in the current-member context of top dimension members. You make the MDX context smaller by using a slicer (the Where clause), which overrides the built-in top-dimensional context. Additionally, you can override the slicer context by specifying context in the search condition argument for Filter.

The following query returns the Profit values for Western Region, for Qtr1. Note that the MDX context is West, Qtr1.

SELECT
  { [Profit] }
ON COLUMNS,
 [Product].levels(0).members
ON ROWS
FROM Sample.Basic
Where (West, Qtr1)

When adding a filter to the above query, the values for Profit are still evaluated as (Profit, West, Qtr1), because the sub-context for Filter is based on the main context.

SELECT
  { [Profit] }
ON COLUMNS,
 Filter( [Product].levels(0).members, Profit < 0)
ON ROWS
FROM Sample.Basic
Where (West, Qtr1)

In the next query, the values for Profit are evaluated as (Profit, West, Qtr1), even though the outer context is (Profit, Market, Qtr1). This is because the inner context in the Filter function overrides the outer context of the slicer (West replaces Market).

SELECT
  { [Sales] }
ON COLUMNS,
Filter( [Product].levels(0).members, (Profit, West) < 0)
ON ROWS
FROM Sample.Basic
Where (Market, Qtr1)

The above query returns the Sales values for West, Qtr1 for members of Product whose Profit for West, Qtr1 was less than 0.

Additional Examples

The following query on Sample Basic returns Qtr2 sales figures for products where the sales have increased by at least 10% since Qtr1.

SELECT 
{ 
  Filter (
    [Product].Members,
    [Measures].[Sales] >  
    1.1 * 
      ( [Measures].[Sales], [Year].CurrentMember.PrevMember )
  ) 
}
on columns
FROM sample.basic
WHERE ([Year].[Qtr2], [Measures].[Sales])

The following query on Sample Basic returns sales figures for product family "100" where the monthly sales of that product family are greater than 8,570. The filtering logic is stored as a named set in the WITH section.

WITH SET [High-Sales Months] as 
' 
 Filter( 
 [Year].Levels(0).members, 
 [Measures].[Sales] > 8570 
 ) 
' 
SELECT 
   {[Measures].[Sales]} 
ON COLUMNS, 
    {[High-Sales Months]}    
ON ROWS 
FROM 
  sample.basic 
WHERE 
 ([Product].[100])