Filter by project type and except components

Questions & Answers
1

I want to show all resolved issues from a specific project type “IT Operations” but also want to except 3 components from this. For this i tried to create a calculated member type in Project dimension but i can’t figure out the right syntax. I tried this:

Aggregate(
  [Project].[IT Operations],
  Except(
    [Project].[Component].Members,
    {
      [Project].[Component].[Service Request],
      [Project].[Component].[Incident],
      [Project].[Component].[Deployment]
    }
  )
)

But this formula is not valid. Can anyone help me out with this?
Thanks in advance.

2

Hi ManfredCF,

From the use case it looks like the filter function within Aggregate might be of use in this case?

Return all resolved issues where the project = IT Operations And Components are not service request, incident, or deployment.

Aggregate(
Filter([Issue].[Issue].Members,
… filter conditions… )
)

– Malik Graves-Pryor

3

Hi,

To aggregate one dimension members except for particular members, you may use function Except(). For this function, you should define a set of all members and a set of members which should be excluded. It is essential, that members in both sets have the same hierarchy level (in your case, components).

For example, the first set should contain components of project “IT Operations”, and the second set should contain particular components you would like to exclude.

Aggregate(
  Except(
    --set of all components for project IT Operations
    [Project].[IT Operations].Children ,
    --set of excluded components
    Filter([Project].[Component].Members,
      [Project].CurrentHierarchyMember.Name 
        MATCHES 'Service Request|Incident|Deployment')
))

Another example, if “IT Operations” is a project category.

Aggregate(
  Except(
    --set of all components for project category IT Operations
    Filter([Project.Category].[Component].Members,
      [Project.Category].CurrentHierarchyMember.Parent.Parent = 
        [Project.Category].[IT Operations]),
    --set of excluded components
    Filter([Project.Category].[Component].Members,
      [Project.Category].CurrentHierarchyMember.Name 
        MATCHES 'Service Request|Incident|Deployment')
))

The second formula uses project hierarchy Category for calculations, therefore remember to select Project.Category when defining a calculated member.

Best,
Zane / support@eazyBI.com