Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter on Multiple tables using a Master table

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's avatar
    Anonymous
    5 years ago

    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.

     

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    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.