Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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 🙂
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 🙂
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()
)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
95 | |
82 | |
71 | |
64 |
User | Count |
---|---|
116 | |
106 | |
94 | |
79 | |
72 |