How to count Tempo Team Members who are not members of another specified team

Questions & Answers
1

Our Tempo teams configuration divides our teams into product teams and geography-based teams. An offshore team member would normally be a member of at least 2 teams - one for the product they support and one for the geography where they work, eg. Widget Team and Pune Team. However, our US-based members are only assigned to a product team. We have no geography-based team defined for US staff. Without having to create another team for our US staff, I want to count the number of US-based staff who have logged time by selecting those users who have logged time and are NOT members of our geography-based teams.

I tried creating a calculated member using the aggregate function with the exclude option, but the counts were too high.

Aggregate(
Except([Logged by Team].[Logged by Team].Members,
{
[Logged by Team].[Pune Team],
[Logged by Team].[PHP Team]
}
)
)

I would appreciate any suggestions on how I can count logged users who are not members of the excluded teams.

2

Hi @dcuozzo,

Since the US-based members are only members of product teams - their logged time would not overlap within teams. Therefore, the total amount of time will be equal whether it takes a sum of time logged under teams or the total time logged under “any team”.

The time logged for the offshore team would always be logged under at least two teams - the geographical team and the product team. Therefore, the sum of time logged under each team will be higher than the overall time logged under “any team”.

You can iterate through all the teams and sum the time logged under each team with the following expression.

Sum(
  DescendantsSet([Logged by Team].CurrentHierarchyMember,
               [Logged by Team].[Logged by Team]),
  [Measures].[Hours spent])

You might retrieve the time logged for “any team” by addressing

 [Logged by Team].DefaultMember

So the full expression for counting US staff would look similar to this.

Count(
 Filter(
--generate set of all assignees for filter
  DescendantsSet([Assignee].CurrentHierarchyMember,[Assignee].[Assignee]),
--condition for logged hour match
   CoalesceEmpty(
    Sum(
     DescendantsSet([Logged by Team].CurrentHierarchyMember,
                    [Logged by Team].[Logged by Team]),
     [Measures].[Hours spent]),1)
  =
 CoalesceEmpty(
    ([Measures].[Hours spent],
     [Logged by Team].DefaultMember),0)
))

Regards,
Oskars / support@eazyBI.com

3

@oskars.laganovskis Thank you for the reply. I failed to mention that everyone is also part of another team used for an accounting integration, and every team member may be part of multiple (variable # of) product teams. If I understand your suggested solution, these additional details will prevent it from working as expected.

Do you have another suggestion? If I know the names of all of the teams, could I create a counter for each Logged by User that would be 1 if that person is a member of any of the selected teams? I could then setup 3 counters one for US, Offshore 1, Offshore 2.

Thanks!
Dave