Is it possible to get all the teams a person is in? In the case of Logged by and Logged by Team

Hi There :wave:

If PersonX is on multiple teams, the “hours spent” should be halved if the person’s team (logged by Team = TeamA) is included in the set of teams associated with PersonX.
To clarify:
Teams of PersonX = TeamA, TeamB.
Logged by Team = TeamA
If the value of the cell “logged by team” is in the “Team of PersonX”, the value “hours spent” will be halved.
Is there a way to determine all teams (as “logged by team”) of a person?

Hi @Friedemann

Welcome to the Community! :sunny:

The Logged by dimension members have the property “Groups”, you can get this property by defining a new calculated measure in the Measures dimension with the following formula:

[Logged by].CurrentHierarchyMember.Get('Groups')

You can then count the number of Groups that each user is a member of by counting the number of commas in the string of groups with a formula like this:

(1 + Len([Logged by].CurrentMember.Get('Groups')) - Len(Replace([Logged by].CurrentMember.Get('Groups'), ',', '')))

However, there may be cases when a user has no group, then you can check with an IIf statement and return the number of groups as 0 like this:

  NOT IsEmpty([Logged by].CurrentMember.Get('Groups')),
  (1 + Len([Logged by].CurrentMember.Get('Groups')) - Len(Replace([Logged by].CurrentMember.Get('Groups'), ',', ''))),

Next, you can divide the predefined “Hours spent” measure by the newly created “Number of Groups” measure (again, here, if the number of groups is zero, then the regular “Hours spent” is returned for the user):

[Measures].[Number of Groups] > 0
[Measures].[Hours spent]
[Measures].[Number of Groups]
[Measures].[Hours spent]

With these measures, you can achieve a report like this:

This same logic can be applied using the Logged by Group dimension in Rows.
You can define a new calculated measure in the Measures dimension “Hours spent - corrected” with the following formula:
  [Logged by Group].CurrentHierarchyMember.Caption = "(no group)",
  [Measures].[Hours spent],
      Descendants([Logged by].CurrentMember,[Logged by].[User]),
      CoalesceEmpty([Logged by].CurrentMember.Get('Groups'),"") MATCHES ".*"||[Logged by Group].CurrentHierarchyMember.Name||".*"
    [Measures].[Hours spent]
    (1 + Len([Logged by].CurrentMember.Get('Groups')) - Len(Replace([Logged by].CurrentMember.Get('Groups'), ',', '')))

This formula will, for each group(row), go through all users and check if they are part of this group, if yes, then the “Hours spent” by the Logged by user will be divided by the number of groups they are a member of. This formula sums this divided number of hours of all of the users that belong to the current group (in the current row).
This should result in adjusted hours spent, and they should match with the total Hours spent by Logged by user:

If you wish to find out more about the MDX functions used in these formulas, you can find their descriptions here in our documentation: MDX function reference

​Let me know if this fits your use case or if you have any additional questions on this!
​Best regards,
​Nauris / eazyBI support