Forum Discussion
Filter in Union Function
Hi i have 3 tables
1 is employee list
| Employee ID |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
2 is Employee Status
| Employee ID | Employee Status |
| 1 | Active |
| 3 | Active |
| 4 | Terminated |
| 5 | Active |
| 7 | Active |
| 8 | Terminated |
I want to calculate total active employee based on table 1 and table 2 , the employee ID is distinct
My idea is use this function but i lost where i should put filter on table 2 where employee status = "active"
My Measure =
COUNTROWS (
DISTINCT (
UNION (
VALUES ( Table1[EmployeeID] ),
VALUES ( Table2[EmployeeID] ),
)
)
)
Please help
Hi laurent_rio ,
Please use the following measure.
My Measure = CALCULATE(DISTINCTCOUNT('Employee ID'[Employee ID]),FILTER('Employee ID',LOOKUPVALUE('Employee Status'[Employee Status],'Employee Status'[Employee ID],'Employee ID'[Employee ID]) = "active"))If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
3 Replies
laurent_rio , You need to a common table
Employee ID = DISTINCT (
UNION (
all ( Table1[EmployeeID] ),
all ( Table2[EmployeeID] ),
)
)You can now join it with both table and get status from table 2 in visual
Or add a new column in Employee ID table
maxx(filter(Table2, Table[Employee ID] = 'Employee ID'[Employee ID]), Table2[Employee Status])
laurent_rio You don't need a UNION here - union will stack both tables on top of each other.
Does the status table have each employee multiple times? Is there a date field to indicate the most recent entry and therefore current status?
Are these two tables related in the data model?
Hi laurent_rio ,
Please use the following measure.
My Measure = CALCULATE(DISTINCTCOUNT('Employee ID'[Employee ID]),FILTER('Employee ID',LOOKUPVALUE('Employee Status'[Employee Status],'Employee Status'[Employee ID],'Employee ID'[Employee ID]) = "active"))If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
