how to handle a Change in dimension table after specific date

[novicepower]

 I have a problem where I am not sure which direction to go.  The info is for a reservation call center where I have two fact tables that contain data from two different databases and a dimension table that contains employee name, unique ID, and department (this table does not contain a date column).  The unique IDs relate to both fact tables.  I have visuals that make comparisons between departments based on employee ID.  The problem:  employee transferred from one department to another and they still utilize the same unique ID.  All data is associated to the employee based on the ID number.  The fact tables do not contain department information.  How can I go about having the data for the employee show the correct department they were tied to if the selected date is after they transferred to a different department? For instance, I have a table showing how many contacts were made by each department.  The employee made 300 contacts while they were with department A and after transferring had 50 contacts within department B.  My dimension table still shows the employee with department A and the data shows the employee made 350 contacts with department A.  How can I have it reflect correctly if the date selected is after the transfer date for that individual person.  All of this is predicated on one person only. 

[v-dineshya · Accepted solution]

Hi @novicepower ,

Thank you for reaching out to the Microsoft Community Forum.

Please refer below to implement a Type 2 Slowly Changing Dimension.

1. Create a new versioned dimension table with Effective Start Date and Effective End Date like below.

EmployeeID   Name       Department    StartDate      EndDate
123             John Smith     Dept A        2023-01-01   2024-06-30
123            John Smith      Dept B         2024-07-01  NULL

Note: you can use "9999-12-31" or BLANK() or NULL for current records.

2. Import it as a new dimension table into Power BI.

3. You cannot use a direct relationship between FactTable.EmployeeID and DimEmployeeHistory.EmployeeID because you need to match on date ranges. Use DAX measures or calculated columns to filter the correct department.

4. Use below DAX Measure to Get the Correct Department, fact table has a ContactDate column.

CorrectDepartment =
VAR SelectedDate = MAX(FactTable[ContactDate])
RETURN
CALCULATE(
MAX(DimEmployeeHistory[Department]),
FILTER(
DimEmployeeHistory,
FactTable[EmployeeID] = DimEmployeeHistory[EmployeeID]
&& SelectedDate >= DimEmployeeHistory[StartDate]
&& SelectedDate <= DimEmployeeHistory[EndDate]
)
)

Note: Use this measure in visuals to show the correct department based on the date of the contact.

5. In a table or matrix visual, use CorrectDepartment instead of the static department from the original dimension. You can also group by this measure to get department-level aggregations.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[novicepower]

@v-dineshya Thank you so very much for your detailed reply!!!.  

[parry2k]

@novicepower this is a common scenarion in data modelling. Read this here for understanding, and/or also search for slowly changing dimension topic to get more details.