MDX Essentials: Basic Set Functions: The Filter() Function - Page 2

The Filter() Function

The Filter() function, according to the Analysis Services Books Online, "returns the set resulting from filtering a specified set based on a search condition." That is, it returns a subset of the specified set based upon the action of a filter, whose operation is selectively enforced by a condition in the function. As we shall see, the uses of the function are varied, and its innovative employment with the correct logic can result in the efficient delivery of very precise results.

We will examine the syntax for the Filter() function in general, and then will undertake practices exercises to reinforce the concepts. We will first create an unfiltered query, and then build to the placement of the function into the query, to meet an illustrative business requirement. In this way, we will be able to see clearly how Filter() operates to create a subset from a set that is already known to us.

Next, we'll explore a more complex scenario where we make innovative use of the Filter() function to meet a more sophisticated requirement. This will give us a richer understanding of the potential that the Filter() function offers, and allow us to examine it from a couple of different perspectives.

Discussion

Filter() allows us to filter a set based upon a particular search condition. In other words, the function allows us to return a subset of a set that the query extracts from a cube, based upon whether it meets certain criteria. Members of the retained subset are returned in their natural order. While the basic use of the Filter() function is quite straightforward, we can use it to return complex results, as potential combinations with other functions and operators are myriad.

Examples that occur in a business scenario might include the need to see only the members of a group of employees whose organizational salary costs have increased over the prior year, or to perhaps return a list of customers (out of the larger set of all customers) whose average monthly expenditures at our retail outlets have decreased.

Let's look at some syntax illustrations to further clarify the operation of Filter().

Syntax

Syntactically, the set to be returned by the Filter() function is placed within the parentheses to the right of Filter, and separated by a comma from the specified criteria that the returned subset is to meet, or the search condition applied. The syntax is shown in the following string:

Filter(<<set>>, <search condition>)

Remarks

The Filter() function returns the tuples of the specified set that meet the criteria of the search condition. The returned subset represents the portion of the larger, specified set that evaluates as "true" with regard to the logical expression contained in the Boolean search condition.

The order of the returned subset is the same as that found in the larger specified set. If there are no tuples in the specified set for which the search condition is true, an empty set is returned by the function.

The following example expression illustrates use of the Filter() function in its most basic form. Suppose we are asked by a group of FoodMart information consumers to present total Warehouse Sales for the cities in which we operate warehouses, where the total 1998 sales of each city is $ 10,000 and above. We might approach this need with an expression similar to this:

FILTER (

[Warehouse].[City].MEMBERS,

    ([Measures].[Warehouse Sales], [Time].[1998]) > 9999)

The Filter() function might be housed in a query like the one below:

SELECT
     {[Measures].[Warehouse Sales]} ON COLUMNS,
     {Filter (
         [Warehouse].[City].MEMBERS,
             ([Measures].[Warehouse Sales], [Time].[1998]) > 9999)
                  } ON ROWS
FROM
    [Warehouse]
WHERE
    ([Time].[1998])

The query, with the measure Warehouse Sales specified for the column, the results of the Filter() function specified for the rows, and the year 1998 from the Time dimension used as slicer, would result in the return of the set depicted in Table 1.

In the example above, we use the Filter() function, in combination with the .Members function (see MDX Members: Introducing Members and Member Functions for a tutorial on this and related family functions), to enumerate the cities for whom the data reflects warehouse sales of greater than $ 9,999.

We will get a chance to see these components in operation again, as we practice the use of the Filter() function in the section that follows. We will look at a relatively simple instance, then a slightly more complex one, as we explore the use of the syntax we have discussed, within the steps of our examples.