How to use multiple tables to relate a task to a group

[DCS2011]

I have two table that have hierarchy and a task table, but I need to reference one to get the other binned correct.

Table 1 (Groups with manager and parent)

Name	Manager	Parent
Eng	Johnny	Corp

Used to create hierarchy for this with 3 levels (max path length)

Table 2 (Personnel Org Chart)

Name	Reports To	Job Title
Johnny	Jonny's Boss	Man, Eng
Mike	Johnny	Johny's Minion
Joan	Johnny	Johnny's Other Minion

Again created a hierarchy with 8 levels.

Then there is a 3rd table that has tasks assign by Org. This table has a relationship with Table 1, that is working.

Task	Assignee
Task 1	Mike
Task 2	Joan
Task 3	Johnny

How can I relate these to show that Eng has 3 total tasked assigned to them. 

TIA!!!

[Anonymous]

Hi @DCS2011 ,

Here I create a sample to have a test.

Table1:

Table2:

Table3:

Data model:

Measure:

Count = 
VAR _Manager =
    MAX ( 'Table 1'[Manager] )
VAR _NAME =
    CALCULATETABLE ( VALUES ( 'Table 2'[Name] ) )
RETURN
    IF (
        ISBLANK ( MAX ( 'Table 1'[Name] ) ),
        BLANK (),
        CALCULATE (
            COUNT ( 'Table 3'[Task] ),
            FILTER (
                'Table 3',
                'Table 3'[Assignee]
                    IN _NAME
                    || 'Table 3'[Assignee] = _Manager
            )
        )
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DCS2011]

Thank you I will test later today or tomorrow and report back.

Here is a follow up question in the mean time. Will it matter if a manager oversee more than one group?I will also confirm, but I think that relationship will be many to many.