Filter on Multiple tables using a Master table

[Anonymous]

Hello Everyone, 

Newbie here trying to create a measure that will use Employee master table (that contains all the employees info without duplicates) to check for each row in the Employee master table, using Employee ID, if this employee has completed a course (Completed Table using completion status column) OR if the employee is registered (Registered Table using registration status column).

The purpose

I would like to filter out all the employees that have completed the course OR that have registered for the course. and left with a list of the employees that have neither completed nor registered for the course. 

The result :

is to have a list of employees that do not appear in the completed or registered tables. 

We will use this list contact the employees that have neither completed nor registered for the course to remind them.

*****************************************************************************************************************

Employee master table

 

Completed Table

Registered Table

Data Model

* This is just an example of the tables that I have. The original tables have hundreds of rows.

** the data model is just for illustration purposes. I know that relationships should be many-to-one.

I will be really grateful if someone can help  me or at least guide me towards a solution.

Thank you in advance

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can create a calculated column to get the flag whether the employee take course or not:

Flag for take courses =
VAR _curemp = 'Employee master'[Employee ID]
VAR _compemp =
    CALCULATETABLE (
        VALUES ( 'Completed'[Employee ID] ),
        FILTER ( ALL ( 'Completed' ), 'Completed'[Employee ID] = _curemp )
    )
VAR _regemp =
    CALCULATETABLE (
        VALUES ( 'Registered'[Employee ID] ),
        FILTER ( ALL ( 'Registered' ), 'Registered'[Employee ID] = _curemp )
    )
RETURN
    IF ( _curemp IN _compemp || _curemp IN _regemp, "Yes", "No" )​

Best regards
Rena
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.