Display of Recruitements and Terminations via Department in a bar char

[Schmidtmayer]

I am lost here.

Following Fact Table is provided:

employee (personal number),
date,
departement_id (the current departement of employee e at date d)
recruitement  (1 if employee e was recruited at date d, 0 otherwise)
termination (1 if employee e was terminated at date d, 0 otherwise)

Following dimension tables are provided:

Employees (active connection to Fact Table via personal number)
personal number
name

Departements (active relationship to Fact Table via Departement_id)
Departement_id,
name

Dates (active relationship to FactTable via date)
date,
month, year, etc.

It is my job to provide a bar char providing a display of terminations and recruitements via Departements with time filter and via time with departement filter. Seems easy enough.
I wrote two measures:

Recruitements = SUMX(FactTable, recruitement) 

Terminations = SUMX(FactTable, -termination) [gives a better view in the bar chart]

Then, selecting bar char as a visual, dropping name from departement table as axis, dropping Recruitements and Terminations as values, should be done!
It is not. Unfortunately, all our employees join some kind of welcome departement after recruitement. After some days they are transferred to their real departement.
I did the following: I added a column within the Fact Table, displaying the first departement_id different from the welcome departement, according to departement history, (welcome departement if there is no such departement) so the Fact Table looks like this now:

employee (personal number),
date,
departement_id (the current departement of employee e at date d)
recruitement  (1 if employee e was recruited at date d, 0 otherwise)
termination (1 if employee e was terminated at date d, 0 otherwise),
first_departement_id

Also a new dimensions table was created:

FirstDepartements (active connection the Fact Table via first_departement_id)
first_departement_id,
name

Also, the measures were changed:

Recruitements = CALCULATE( SUMX(FactTable, recruitement),  ALL(Departements[name]))

Terminations = CALCULATE( SUMX(FactTable, -termination), ALL(FirstDepartements[name]))

The display over time with filter for departement is easy now:

Select bar char as visual, drag year and month as axis, set FirstDepartements[name] and Departements[name] as filters, rename them to Departement (Recruitement) and Departement (Termination) in order to show, that these act individually, drag Terminations and Recruitements as values, done.

The other visualisation is not that easy, so Recruitements and Terminations via Departements with time filter.
As the termination is assigned to Departements, the recruitement is assigned to FirstDepartements, I see no way to visualize this.
That's where I need help.

Note:
Following things are used to get the current/first departement:

1) Table Team History:
personal number,
team_id,
start,
team_type (1 for main team, 0 otherwise)

We just care about the main team, so this indeed unique, as every employee just has one main team)

2) Table Teams
team_id,
departement_id (every Team belongs to only one departement)

3) Table Departements
departement_id,
name

Is it possible to get the visual I wish?

[amitchandak · Accepted solution]

@Schmidtmayer , refer if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Schmidtmayer]

@amitchandak : Thanks a bunch, this helped a lot 😃
Did not thought of USERRELATIONSHIP at all -.- I can manipulate the relationship between the Departement Table and the Fact Table using this function. For Recruitements the active relationship being different from the active one in Terminations 😃
Will provide some photos after holidays, did not thought I would get an answer this quick, so other parts of the report were prioritized.

Greetings Tom