Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Problem counting from two databases based on various conditions

I have two databases one of supervisors and one of subordinates. I need to count the amount of times a supervisor and a subordinate have the same place, shift and Date.

What I have only considers if the conditions are true but doen't count them.

Table of Supervisors

SupDateShiftPlace

Rachel11/12/20230000NY
Rachel11/13/20230000NY
Rachel11/14/20230000NY
Rachel11/15/20230000BT
Rachel11/16/20231600NY
Rachel11/17/20231600AT
Rachel11/18/20230800BT
Brown11/12/20230000AT
Brown11/13/20230000AT
Brown11/14/20230000AT
Brown11/15/20230800NY
Brown11/16/20230800BT
Brown11/17/20230800BT
Brown11/18/20230800NY

Table of subordinates

SubDateShiftPlace

Taylor11/12/20230000NY
Taylor11/13/20230000NY
Taylor11/14/20230000NY
Taylor11/15/20230000NY
Thomas11/16/20231600NY
Thomas11/17/20231600AT
Thomas11/18/20231600BT
Taylor11/12/20230000AT
Taylor11/13/20230000AT
Taylor11/14/20230000BT
Thomas11/15/20230800NY
Thomas11/16/20230800BT
Thomas11/17/20230800BT
Thomas11/18/20230800NY

The correct answer would be

 RachelBrown
Taylor32
Thomas14

 

What I have until now is:

 

 

Same = 
IF (
    CONTAINS (
        Sup,
        Sup[Date], MAX(Sub[Date]),
        Sup[Shift], MAX(Sub[Shift]),
        Sup[Place], MAX(Sub[Place])
    ),
    0,
    1
)

 

 

 

Please help