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?
