All eazyBI for Jira eazyBI for Confluence Private eazyBI

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

 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 are in Developers group.

Filter(
  [Reporter].[User].Members, 
  CoalesceEmpty([Reporter].CurrentMember.get('Groups'), '') 
    MATCHES '.*developers.*'
)


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

We suggest using Filter based on Issue properties when addressing a set of issues with Descendants([Issue].CurrentMember, [Issue].[Issue]) whenever possible. For example following formula will filter issues by Issue creation date and resolution date in the same period. Some measure and not property still is needed to retrieve a set of issues relevant to report. If possible use measures outside of the filter to improve the performance.

The formula below uses two issue properties - dates as filter criteria - Issue creation date date and Issue resolution date. Measure Issues created is used outside of the filter to improve the performance. It works both as issue counter for Sum and as an additional filter there as well.

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 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 was created in the same period on a timeline.

See also