I would like to join 2 virtual tables by a common field (EmployeeID)
Grateful if anyone could please help with the syntax; here are the involved tables:
No. Employees in Occupation =
/* Get all active employees, meaning: 1. They have joined the company prior to the valuation month 2. They still haven't left the company at the end of the valuation month The table ActiveEmployees has the following fields: 1. EmployeeID 2. Date_Joined 3. Date_Left */ VAR ActiveEmployees = FILTER( 'EmployeesInCompany', 'EmployeesInCompany'[Date_Joined] < [Valid From AY] && 'EmployeesInCompany'[Date_Left] >= [End Month AY] ) /* Get all employees in their active occupation i.e. 1. The Occupation has been joined prior to the valuation month 2. The employees still haven't left the occupation at the end of the valuation month Table ActiveOccupation has the following fields: 1. EmployeeID 2. Date_Joined 3. Date_Left */ VAR ActiveOccupation = FILTER( 'EmployeeOccupation', 'EmployeeOccupation'[Date_Joined] < [Valid From AY] && 'EmployeeOccupation'[Date_Left] >= [End Month AY] ) /* The aim is to count all employeeid's from ActiveOccupation e.g. Count( distinct ActiveOccupation.EmployeeID ) where ActiveOccupation.EmployeeID = ActiveEmployees.EmployeeID */
Maybe there is a simpler way to do it as well?
Thanks in advance.