Filter

The Filter function returns a set resulting from filtering a specified set based on a search condition. The search condition is applied to each tuple of the set and only those where the logical expression evaluates totrue are returned. If no tuples match the required conditions, an empty set is returned.

Syntax

Filter(Set_Expression, Logical_Expression)

Arguments

Set_Expression	MDX expression that returns a set that will be filtered.
Logical_Expression	MDX logical expression that returns `true` or `false` and will be checked for each member of the set.

Examples

Count of assignees who have assigned issues

Use the measure "Issues created" as a filter condition to get the number of assignees who have assigned issues.

Count(
  Filter( 
    [Assignee].[User].Members,
    [Measures].[Issues created] > 0
  )
)

Aggregate a set of Projects by a specific Project Lead

Filter all projects by their property "Project lead" to find all those where the lead is "Admin."

Aggregate(
  Filter(
    [Project].[Project].Members,
    [Project].CurrentHierarchyMember.Get('Project lead') = 'Admin'
  )
)

Aggregate a set of Week days that are not weekend

Filter and aggregate all dimension "Week Day" members by their name that doesn't match "Saturday" or "Sunday".

Aggregate(
  Filter(
    [Week Day].[Week Day].Members,
    [Week Day].CurrentHierarchyMember.Name NOT MATCHES "Saturday|Sunday"
  )
)

Filter a set of issues that have the label 'test'

Filter and count all issues by their property "Issue labels" that contain "test" syntax. Use MATCHES and regular expressions for more accurate filtering, as this field can contain multiple values.

Count(
  Filter(
    Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    [Measures].[Issue labels] MATCHES '.*test.*'
  )
)

Filter and sum issues that have their creation date and resolution date in the same period

Exercise caution when iterating through all issues, as the set can become very large. This approach should only be used when faster calculations using Tuples are not feasible.
When a specific set of issues is needed in your formula, then:

- First, gather a set of all issues in the necessary hierarchy and level using Descendants.
- Next, apply a Filter function to this set and specify the filtering conditions. The fastest way to filter issues is by using issue properties in the filter conditions.
- Finally, apply the Sum function to the Filtered set to sum the value of a specific measure for this set of issues.

The example formula uses two issue properties—creation date and resolution date—as filter criteria. The measure "Issues created" is used outside the filter to enhance performance, acting as an issue counter for Sum and an additional filter.

Sum(
  Filter(
    Descendants([Issue].Currentmember, [Issue].[Issue]),
    DateInPeriod(
      [Measures].[Issue created date],
      [Time].CurrentHierarchyMember
    )
    AND
    DateInPeriod(
      [Measures].[Issue resolution date],
      [Time].CurrentHierarchyMember
    )
  ),
  [Measures].[Issues created]
)

See the example report Issues created and resolved in period % in our demo account. The calculated measure Issues created and resolved in period uses the formula above to count resolved issues that were created in the same period on a timeline.