Check if items appear in hierarchy with multiple criteria

[marcel97]

Hello all,

I am managing a tool in my cooperation's project management office, where team leads are required to add their respective team members. 

As I suspect that some team leads are not maintaining their team members properly, I want to compare the list of resources form that tool with an official employee list from HR.

In that list from HR I find first and last name and the team name of the resource/employee/team member:

HR_DATA

Team	First Name	Last Name
ADW	Division	Head
ADWS	Department	Head
ADWS1	Team	Lead 1
ADWS1	Team	Member 11
ADWS1	Team	Member 12
ADWS1	Team	Member 13
ADWS2	Team	Lead 2
ADWS2	Team	Member 21
ADWS2	Team	Member 22
ADWS2	Team	Member 23

The team structure in my tool does not stop at the ADWSx level, but has even lower levels than that:

TEAM

ID	Parent_ID	Team Name	Path
1		ADW	1
2	1	ADWS	1|2
3	2	ADWS1	1|2|3
4	3	ADWS11	1|2|3|4
5	2	ADWS2	1|2|5
6	5	ADWS21	1|2|5|6
7	6	ADWS211	1|2|5|6|7

I also have a resource table in which all the employees with their respective team are listed

RESOURCE

Team	First Name	Last Name
ADW	Division	Head
ADWS	Department	Head
ADWS1	Team	Lead 1
ADWS1	Team	Member 11
ADWS11	Team	Member 12
ADWS11	Team	Member 13
ADWS2	Team	Lead 2
ADWS2	Team	Member 21
ADWS21	Team	Member 22
ADWS211	Team	Member 23

I now want to find out, whether all the resources that are listed in HR_DATA are also to be found in the same team or one of its subteams in my tool. Whats the best way to find out?

Thanks and kind regards

Marcel

[v-kkf-msft · Accepted solution]

Hi @marcel97 ,

Please try the following calculation columns:

FormatTeam = 
VAR length =
    PATHLENGTH ( RELATED ( TEAM[Path] ) )
VAR teamname =
    CALCULATE (
        MAX ( TEAM[Team Name] ),
        FILTER (
            ALL ( TEAM ),
            TEAM[ID] = VALUE ( PATHITEM ( RELATED ( TEAM[Path] ), 3 ) )
        )
    )
RETURN
    IF ( length <= 3, RELATED ( TEAM[Team Name] ), teamname )

IsContains = 
COUNTROWS (
    INTERSECT (
        { ( RESOURCE[FormatTeam], RESOURCE[First Name], RESOURCE[Last Name] ) },
        HR_DATA
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@marcel97 , do we have employee ID in table 1 and 3? like we have in table 2 - ID

[marcel97]

Hey @amitchandak , table 1 does not have any IDs as it comes directly from HR and not from my tool. 

I simplified table 3 for data protection, but it does have IDs and would look like this:

ID	Team	First Name	Last Name
1	ADW	Division	Head
2	ADWS	Department	Head
3	ADWS1	Team	Lead 1
4	ADWS1	Team	Member 11
5	ADWS11	Team	Member 12
6	ADWS11	Team	Member 13
7	ADWS2	Team	Lead 2
8	ADWS2	Team	Member 21
9	ADWS21	Team	Member 22
10	ADWS211	Team	Member 23

Kind regards

Marcel