Filter in Union Function

[laurent_rio]

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

[v-deddai1-msft · Accepted solution]

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

[AllisonKennedy]

@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?

[amitchandak]

@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])