Calculated Members - How to filter based on project component in EazyBI?

Hi

My requirement is to have 2 calculated members on the Project domain; to filter out the jobs that two separate teams will have worked on.

 

Here's the proposition....

We have two separate development teams that work on 3 separate projects, for simplicity let's call the teams "Team A" and "Team B" and the projects "Project X", "Project Y" and "Project Z".

"Team A" will work on

  • jobs in "Project X" where the Component is "Component 1" or "Component 2"
  • jobs in "Project Y" where the Component is "Component 1" or "Component 3"
  • jobs in "Project Z" where the Component is "Component 1" or "Component 2 " or "Component 3" or "Component 4"

"Team B" has a much more complex way of working and will work on all the other components in each project bar the ones worked on by Team A i.e.

  • jobs in "Project X" where the Component is "Component 5"  all the way to "Component 20"
  • jobs in "Project Y" where the Component is "Component 15" all the way to "Component 30"
  • jobs in "Project Z" where the Component is "Component 5" all the way to "Component 12" 

I want to be able to have two calculated members on the project domain that filter out all the issues according to the rules above.

In trying to implement this I first built this calculated member which I called "Team A Project Jobs"....

 

Aggregate({
[Project].[X].[Component 1],
[Project].[X].[Component 2],
[Project].[Y].[Component 1],
[Project].[Y].[Component 3],
[Project].[Z].[Component 1],
[Project].[Z].[Component 2],
[Project].[Z].[Component 3]
})

 

This appears to work - but in defining "Team B Project Jobs" I don't want to name each component.   I would have thought that I could use an approach below - but that appears to still bring through the components that Team A work on 

Aggregate(
Except(
[Project].[Component].Members,
{
[Project].[Team A Project Jobs]
}
)
)

Please can you help - I'm 4 hours into trying to crack this one and I'm losing my mind?

 

2 answers

Without wishing to sound desperate (although I am), please can anyone advise?

Hi Graem,

I would suggest extracting the numeric part from the component name and then do some numeric operations with extracted number.

 

You could try this formula for calculated member 'Team B Project Jobs':

Aggregate(
   Filter(
     [Project].[Component].Members,
     ([Project].CurrentHierarchyMember.Parent.Name = 'Project X' 
         AND
         Val(ExtractString([Project].CurrentHierarchyMember.Name, '.* (\d+)', 1)) >= 5
         AND
         Val(ExtractString([Project].CurrentHierarchyMember.Name, '.* (\d+)', 1)) <= 20
     )
     OR
     ([Project].CurrentHierarchyMember.Parent.Name = 'Project Y' 
         AND
         Val(ExtractString([Project].CurrentHierarchyMember.Name, '.* (\d+)', 1)) >= 15
         AND
         Val(ExtractString([Project].CurrentHierarchyMember.Name, '.* (\d+)', 1)) <= 30
     )
     OR
     ([Project].CurrentHierarchyMember.Parent.Name = 'Project Z' 
         AND
         Val(ExtractString([Project].CurrentHierarchyMember.Name, '.* (\d+)', 1)) >= 5
         AND
         Val(ExtractString([Project].CurrentHierarchyMember.Name, '.* (\d+)', 1)) <= 12
     )
    )
)

 

 

Daina / support@eazybi.com

Thanks for taking the time to answer this Daina.  Unfortunatley, in the example, for clarity I gave component names such as Component 1 and Component 2 etc.  However, in reality the components are named such as ".NET" and "MySQL" and "Oracle" etc etc sad

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Thursday in Confluence

Three common content challenges + how to manage them

An efficient enterprise content management system, or ECM, is a must-have for companies that create work online (cough   cough, all companies). If content calendars, marketing plans, and bu...

97 views 0 6
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you