Link 2 virtual tables; is it possible?

DAX DAX Calculations
1

Hi everyone,

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.

Kind regards,
Yogesh

1 Like
2

I’m just not sure you need to be thinking about linking virtual tables here. Feels to me that it over-complicating it a little bit.

If you need to join up two physical table like this, I would complete it by merging within the query editor. This would be very quick and easy to do and simplify every calculation you complete after this point.

If you really need to do it this way then potentially you and just comparing two table and you could use the INTERSECT function, then wrap this with COUNTROWS.

Check out the example here for a scenario on this.

1 Like
3

Hi Sam,

Thanks for your reply.

In fact, all the employee variables like Entry in the Group, Entry in a particular department, Getting assigned to a particular occupation (i.e. job), etc. are date based.

For example, in 1 particular month, 1 employee can change jobs more than once (doesn’t happen often, but it has happened).

That’s why, for each month selected (via a slicer), first I get all the active employees (using the table ActiveEmployees from my previous post) and then I use this as a base table to filter out all other date-based tables.

I will definitely give the INTERSECT function a try.

Kind regards,
Yogesh