Forum Discussion

aflintdepm's avatar
aflintdepm
Icon for Helper III rankHelper III
2 years ago
Solved

Count Rows on another table based on attributes on main table

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 ...
How-to
  • Samarth_18's avatar
    Samarth_18
    2 years ago

    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 -

     

     

Sahir_Maharaj's avatar
Sahir_Maharaj
Icon for Super User rankSuper User
2 years ago

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!