NEED HELP Compare columns in two tables and Find Difference

[bboobe]

Hi Experts 

I am trying to compare a master file with daily employee file 

In Master table, I have unique department code for each Project code  

In Employee table, I have more than one department code for a single project as the department is tagged incorrectly by the employee level.

I am stuck with this now. Please advise.

Below is the Sample - 

Employee Table Project ID - 12345 

Employee Table Department ID - 100

Master table Departement id - 101 

Result - False

[nandukrishnavs · Accepted solution]

@bboobe 

Master Table

Project ID	Department ID
1200	101
1201	120

EmployeeTable

Project ID	Employee ID	Department ID
1200	1	100
1201	2	120

Status Check = 
var _selectedDepartment= CALCULATE(SELECTEDVALUE(EmployeeTable[Department ID]))
var _selectedProjectId= CALCULATE(SELECTEDVALUE(EmployeeTable[Project ID]))
var _actualDepartment= CALCULATE(LOOKUPVALUE(MasterTable[Department ID],MasterTable[Project ID],_selectedProjectId))
var _result= IF(_actualDepartment=_selectedDepartment,TRUE(),FALSE())
return _result

 

If this is not working, please share your sample tables with 5 rows and expected output.

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[bboobe]

Thank you @nandukrishnavs  It worked. 🙂 

[az38]

@bboobe 

there is not full data model but it may looked like

Measure = 
var _curDepartment = MAX('Master'[Departement id])

RETURN
IF(
CALCULATE(COUNTROWS('Employee'), FILTER(ALL('Employee'), 'Employee'[Departement id]=_curDepartment) ) > 0, 
TRUE(), 
FALSE()
)

[bboobe]

Thank you for taking time and responding @az38 . I tried the above measure and it did not work. Below is the screenshot from the visual.

The comparison has to be done on employee level. and i need to view only the employee ids with incorrect department.