The purpose of the Filter() function is to filter out parts of a set that is not required in a given situation and the function returns a subset of a larger set. Uses of the Filter() function can range from the sublimely simple to the impressively advanced, and it can be used in many innovative ways. The objective, of course, is the support of precise analysis to meet the business needs.

Syntax of MDX Filter function:

Filter(Set_Expression, Logical_Expression )

Example MDX Filter function:

Below query will return measure [Measures].[Warehouse Sales] on columns and [Warehouse].[City].MEMBERS on rows for the year 2008.


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


Suppose, the business requirement is that the query should display only those cities where the sales amount for year 2008 is less than $10000, then we can use Filter() function effectively.

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

The Filter function evaluates the specified logical expression against each tuple in the specified set. The function returns a set that consists of each tuple in the specified set where the logical expression evaluates to true. If no tuples evaluate to true, an empty set is returned.