Compare values from columns in two tables, identify those that don't exist

[Alpha_Wolf01]

I have two tables one of them (Table 1) is reflecting time entry for employees and Table 2 is the Main Database of all employees i have. Table1 & Table 2 are joined by the employee ID. I am trying to create a new table (Table 3) to get the employees that haven't shown or sign-in at any day, Below is a mock-up of what I have....

Table 1 

Employee ID	Sign in
A	16-Mar
A	15-Mar
B	15-Mar

Table 2

Employee ID

A

B

C

Table 3

Table 3
Employee	Date	Employee Sign In
A	16-Mar	TRUE
B	16-Mar	FALSE
C	16-Mar	FALSE
A	15-Mar	TRUE
B	15-Mar	TRUE
C	15-Mar	FALSE

Ideas?

Thanks!

Waleed

[v-xulin-mstf · Accepted solution]

Hi @Alpha_Wolf01,

Create tables as:

Table = 
NATURALLEFTOUTERJOIN('Table 2','Table 1')

Date = 
CALENDAR("2021-3-15","2021-3-16") 

Table 3 = 
CROSSJOIN('Table','Date')

 Try measure as:

Measure = 
var a=
CALCULATE(
    MAX('Table 3'[Sign in]),
    FILTER(
        'Table 3',
        MAX('Table 3'[Date])='Table 3'[Sign in]
    )
)
return
IF(
    a<>BLANK(),
    "Ture",
    "False"
)

Here is the output:

The demo is attached.

Best Regards,

Link

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Alpha_Wolf01]

Thanks Mate, It works fine 😄

[Alpha_Wolf01]

Hi @v-xulin-mstf ,

Thanks for the Solution provided i appreciated, i have tried to apply your solution but it gave me the below error:

The relation between the two Tables is Many to Many and have both direction, this relation i can't change it to "Many to One" Single Direction, any Idea?

[Alpha_Wolf01]

@v-xulin-mstf, i have solved this previous issue by using the  DISTINCT, since it seems their were duplicates in the ID which i will check later, so now everything is working as you explained, i will continue test and will feed you back.

[selimovd]

Hello @Alpha_Wolf01 ,

you can use the EXCEPT function in DAX for that:

Table 3 = EXCEPT( VALUES( 'Table 1'[Employee ID] ), VALUES( 'Table 2'[Employee ID] ) )

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi

[Alpha_Wolf01]

Hi selimovd,

i have tried your solution but the table is blank, what i am trying to achieve is the following (Sorry for the bad explaination at the Topic:

I have both Table 1 & 2 are joined via the Employee ID, as below:

The Result is i need to have a new table as below (Table 3) showing all the employee's in the database whether if they have signed in or not in each day.