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.
I am trying to compare monthly employee lists and create a list highlighting new and let go employees. I was able to append the monthly datasets and use conditional formatting to highlight individuals that only appeared once. However, this highlighted BOTH new and fired employees in the SAME color (since they only appear once each). I'd like a way to delineate between who is a new employee and who has moved on from the company. Unfortunately, this is on my work computer so I can't give you a screenshot. Below is an example of what I'm shooting for:
Employees
1. Bob
2. Daniel
3. Smith
4. Neo
5. Morpheus
New Employees:
4. Neo
5. Morpheus
Old Employees
2. Smith
New Combined List:
1. Bob
2. Daniel
3. Smith
4. Neo
5. Morpheus
Solved! Go to Solution.
hi,@bw70316
After my research, you can do these follow my steps as below:
Step1:
Assuming that this is this month table
and this is last month data
then I append the last month data
Step2:
Add count column
Count = CALCULATE(COUNT(Append1[Name]),FILTER(Append1,Append1[Name]=EARLIER(Append1[Name])))
Step3:
Use lookupvalue function to search the name in last month data table
last Mon = LOOKUPVALUE(Table2[Name],Table2[Name],Append1[Name])
Step4:
Use this formula :
this Mon Emp = IF(Append1[last Mon]=BLANK()||Append1[Count]>1,Append1[Name])
Result:
Now the rows that count=1 are New Employees and count>1 are old Employees
and this Month Emp is blank are Lost Employees.
Best Regards,
Lin
hi,@bw70316
After my research, you can do these follow my steps as below:
Step1:
Assuming that this is this month table
and this is last month data
then I append the last month data
Step2:
Add count column
Count = CALCULATE(COUNT(Append1[Name]),FILTER(Append1,Append1[Name]=EARLIER(Append1[Name])))
Step3:
Use lookupvalue function to search the name in last month data table
last Mon = LOOKUPVALUE(Table2[Name],Table2[Name],Append1[Name])
Step4:
Use this formula :
this Mon Emp = IF(Append1[last Mon]=BLANK()||Append1[Count]>1,Append1[Name])
Result:
Now the rows that count=1 are New Employees and count>1 are old Employees
and this Month Emp is blank are Lost Employees.
Best Regards,
Lin
Thank you very much, worked on the very first try. May I ask what avenue you took to learn DAX like this... youtube, udemy?
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |