Skip to main content
Skip table of contents

Filter

Returns the set that results from filtering a specified set based on a search condition.

The search condition is applied to each tuple of the set and only the ones where logical expression evaluates to true are returned. In case no tuples match the required conditions, empty set is returned.

Syntax

CODE
Filter(Set_Expression, Logical_Expression )

Arguments

Set_Expression

MDX expression that returns set.

Logical_Expression

MDX logical expression that returns true or false.

Examples

Following example would return all Members who's invoice type is cash.

CODE
Sum(Filter(
  [Invoice].[Invoice].Members, 
  [Invoice].CurrentMember.getProperty('Invoice type') MATCHES 'Cash'
))


You should be very careful, when iterating through all members as the set of members can get quite large. It is suggested to do so only in cases when calculations using Tuples, which are significantly faster, cannot be performed. 

If Filter should work as well for detailed Invoices Descendants([Invoice].CurrentMember, [Invoice].[Invoice Item]) should be used. For example, following formula returns sum of all invoice item quantity in given context. Normally, some further filter condition would be added as the following formula is returning the same as Item quantity measure.

CODE
Sum(Filter(
  Descendants([Issue].CurrentMember, [Issue].[Issue]),
    [Measures].[Issues created] > 0 
), [Measures].[Issues created])
CODE
Sum(Filter(
  Descendants([Invoice].CurrentMember, [Invoice].[Invoice]),
  [Measures].[Item quantity] > 0 
), [Measures].[Item quantity])
JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.