Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Alpha_Wolf01
Frequent Visitor

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

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 IDSign in
A16-Mar
A15-Mar
B15-Mar

 

Table 2

Employee ID
A
B
C

 

Table 3

Table 3  
EmployeeDateEmployee Sign In
A16-MarTRUE
B16-MarFALSE
C16-MarFALSE
A15-MarTRUE
B15-MarTRUE
C15-MarFALSE

 

Ideas?

Thanks!

 

Waleed

1 ACCEPTED SOLUTION
v-xulin-mstf
Community Support
Community Support

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:

v-xulin-mstf_0-1616486562325.png

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.

 

 

 

View solution in original post

6 REPLIES 6
v-xulin-mstf
Community Support
Community Support

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:

v-xulin-mstf_0-1616486562325.png

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.

 

 

 

Thanks Mate, It works fine 😄

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:

Alpha_Wolf01_0-1616674934753.png

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_1-1616675128162.png

 



@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
Super User
Super User

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

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:

 

Alpha_Wolf01_0-1615916943351.png

 

Alpha_Wolf01_1-1615916987346.png

 

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.

 

Alpha_Wolf01_2-1615917008199.png

 

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors