Forum Discussion

aflintdepm's avatar
aflintdepm
Helper 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 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_IDNameStatusTask Count
1111John SmithTerminated2
2222Bob JonesTerminated0

 

EE_Status:

EE_IDNameStatus
1111John SmithTerminated
2222Bob JonesTerminated
3333Jane DoeActive

 

Task table:

EE_IDTask_NameTask_Status
1111VisitComplete
1111VisitIncomplete
1111EvalIncomplete
2222VisitComplete
2222VisitComplete
2222EvalComplete
3333VisitComplete
3333VisitIncomplete
3333EvalIncomplete
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 -

     

     

4 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    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 -

     

     

    • aflintdepm's avatar
      aflintdepm
      Helper III
      2 years ago

      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

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super 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!