Filter

Syntax

FILTER ( set, search_condition )

Parameters

set: The set through which to iterate.
search_condition: A Boolean expression (see MDX Grammar Rules). The search condition is evaluated in the context of every tuple in the set.

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:

Table 4-64 Output Grid from MDX Example

(axis)	Profit
100-10	22777
100-20	5708
100-30	1983
200-10	7201
200-20	12025
200-30	4636
200-40	4092
300-10	12195
300-20	2511
300-30	11093
400-10	11844
400-20	9851
400-30	-394
100-20	5708
200-20	12025
300-30	11093

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:

Table 4-65 Output Grid from MDX Example

(axis)	Profit
400-30	-394

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:

Table 4-66 Output Grid from MDX Example

(axis)	Profit
100-10	22777
100-20	5708
200-10	7201
200-20	12025
300-10	12195
300-20	2511
300-30	11093

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:

Table 4-67 Output Grid from MDX Example

(axis)	IsCaffeinated
100-10	1
100-20	1
100-30	0
200-10	1
200-20	1
200-30	0
200-40	0
300-10	1
300-20	1
300-30	1
400-10	0
400-20	0
400-30	0
100-20	0
200-20	0
300–30	0

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.

Table 4-68 Output Grid from MDX Example

(axis)	Sales
100-20	2153
400-30	1862
100-20	2153

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])

Table 4-69 Output Grid from MDX Example

Cola	Dark Cream
16048	11993

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])

Table 4-70 Output Grid from MDX Example

(axis)	Sales
Apr	8685
May	8945
Jun	9557
Jul	9913
Aug	9787
Sep	8844
Dec	8772