Count Rows on another table based on attributes on main table

[aflintdepm]

I have 2 tables, one is a list of employees with employment status and the other is a list of tasks assigned to those employees with the task status.  Everthing is linked by EE_ID, but I did have to create a table of just unique values to avoid Many:Many between the 2 source tables, so my relationship looks like this:
Task Table *->1 Unique EE_ID 1<-* EE Status

I need create a measure that tells me the list of Incomplete tasks when an Employee is Terminated so those tasks can be reassigned.

I would like to present the results in a table or matrix.

Desired Ouput:

EE_ID	Name	Status	Task Count
1111	John Smith	Terminated	2
2222	Bob Jones	Terminated	0

EE_Status:

EE_ID	Name	Status
1111	John Smith	Terminated
2222	Bob Jones	Terminated
3333	Jane Doe	Active

Task table:

EE_ID	Task_Name	Task_Status
1111	Visit	Complete
1111	Visit	Incomplete
1111	Eval	Incomplete
2222	Visit	Complete
2222	Visit	Complete
2222	Eval	Complete
3333	Visit	Complete
3333	Visit	Incomplete
3333	Eval	Incomplete

[Sahir_Maharaj]

Hello @aflintdepm,

Can you please try the following approach:

IncompleteTasksForTerminated = 
VAR TerminatedEmployees = 
    FILTER (
        'EE_Status',
        'EE_Status'[Status] = "Terminated"
    )

VAR IncompleteTasks = 
    FILTER (
        'Task Table',
        'Task Table'[Task_Status] = "Incomplete"
    )

VAR Result = 
    COUNTROWS (
        FILTER (
            'Task Table',
            'Task Table'[EE_ID] IN VALUES (TerminatedEmployees[EE_ID]) &&
            'Task Table'[Task_Status] = "Incomplete"
        )
    )

RETURN
    Result

Hope this helps!

[Samarth_18 · Accepted solution]

Hi @aflintdepm ,

You could use below measure - 

Measure =
COUNTROWS (
    FILTER (
        'Task table',
        'Task table'[EE_ID] = MAX ( EE_Status[EE_ID] )
            && 'Task table'[Task_Status] = "Incomplete"
            && MAX ( EE_Status[Status] ) = "Terminated"
    )
) + 0

output -

[aflintdepm]

@Samarth_18 
Thank you for the response.  Can you please clarify the function of the MAX in the measure?  Other than that, I believe I understand the syntax

[Samarth_18]

@aflintdepm , It's just to pick a value of EE_ID, Status from the current context.