Forum Discussion
Repeating "unique" employee IDs
Long time listener, first time caller, be gentle! 🙃
I have a table of my team's employees; their unique identifier is their staff number E0000000 etc and the list contains their names, team, location, start date and end date for when they leave the business.
We track revenue, from a table of timesheet entries which uses their staff number in a one to many relationship and is refreshed by an overwritten system export every week (I don't have live access to the data).
Next year, some people will be moving teams but staying within the business, but I need to be able to keep this year's revenue associated with their old team, and revenue going forward associated with their new team.
In the example data below, Shania moves from Team A on 30/11 to Team B on 1/12. How do I show Team A earned their revenue until their move, but Team B afterwards?
Obviously, PBI isn't letting me have two "unique" numbers in the relationship - what could be a solution to achieve the above?
| EmployeeID | FullName | Team | StartDate | EndDate |
| E0000001 | Bob Smith | A | 1/1/2023 | |
| E0000002 | Shania Twain | A | 2/8/2024 | 30/11/2025 |
| E0000003 | Khris Kringle | A | 1/12/2025 | |
| E0000004 | Arnie Black | B | 5/1/2025 | |
| E0000002 | Shania Twain | B | 1/12/2025 |
| Date | ProjectName | ProjectID | Quantity | ResourceName | ResourceID | SalesAmount |
| 29/11/25 | Client ABC | PR0001 | 5.50 | Bob Smith | E0000001 | 687.5 |
| 29/11/25 | Client ABC | PR0001 | 2.50 | Shania Twain | E0000002 | 437.5 |
| 29/11/25 | Client XYZ | PR0102 | 1.50 | Arnie Black | E0000004 | 32.02 |
| 30/11/25 | Client ABC | PR0001 | 7.50 | Bob Smith | E0000001 | 937.5 |
| 30/11/25 | Client ABC | PR0001 | 7.50 | Shania Twain | E0000002 | 1312.5 |
| 30/11/25 | Client XYZ | PR0102 | 7.50 | Arnie Black | E0000004 | 32.02 |
| 01/12/25 | Client ABC | PR0001 | 7.50 | Bob Smith | E0000001 | 937.5 |
| 01/12/25 | Client ABC | PR0001 | 5.00 | Khris Kringle | E0000003 | 525 |
| 01/12/25 | Client ABC | PR0001 | 7.50 | Shania Twain | E0000002 | 1312.5 |
| 01/12/25 | Client QRS | PR0500 | 2.50 | Khris Kringle | E0000003 | 262.5 |
| 02/12/25 | Client ABC | PR0001 | 7.50 | Bob Smith | E0000001 | 937.5 |
| 02/12/25 | Client ABC | PR0001 | 5.00 | Khris Kringle | E0000003 | 525 |
| 02/12/25 | Client ABC | PR0001 | 7.50 | Shania Twain | E0000002 | 1312.5 |
Hi selftaughtmike ,
Yes you can use the same reveneue measure. It will work for Projects, Teams, Date etc. Because our modelling is how Slowly Changing Dimension 2 behave.
Timesheet and Calendar should be in relationship. But no direct relationship between Timesheet and Employee.
Other than that you can use it.
If this solved your issue, please mark it as the accepted solution. ✅
Hello selftaughtmike,
TL;DR:
You can’t use EmployeeID alone as the unique key because employees move teams over time. The accepted solution is to model an Employee–Team history table with date ranges (a slowly changing dimension).
In Desktop, you handle this with relationships and DAX. In Service, you do it with Dataflows/Datamarts. Both approaches ensure historical revenue stays with the correct team.Option 1 – Power BI Desktop
If you’re modeling in Desktop, create an Employee–Team history table:
EmployeeID Team StartDate EndDate E0000002 A 02/08/2024 30/11/2025 E0000002 B 01/12/2025 (blank)Use (EmployeeID + Team + DateRange) as the unique key.
Relate Timesheet → EmployeeTeamHistory.
Add a calculated column in Timesheet:
AssignedTeam = LOOKUPVALUE( EmployeeTeamHistory[Team], EmployeeTeamHistory[EmployeeID], Timesheet[ResourceID], TRUE(), Timesheet[Date] >= EmployeeTeamHistory[StartDate] && (ISBLANK(EmployeeTeamHistory[EndDate]) || Timesheet[Date] <= EmployeeTeamHistory[EndDate]) )
This ensures Shania’s revenue up to 30/11/2025 is attributed to Team A, and from 01/12/2025 onward to Team B.Option 2 – Power BI Service (no Desktop)
If you’re working only in Service, use Dataflows or Datamarts:
Create a Dataflow with two entities:
Timesheet (revenue data).
EmployeeTeamHistory (employee table with StartDate/EndDate).
In Power Query (inside the Dataflow):
Add a composite key column:
EmployeeKey = EmployeeID & "-" & TeamMerge Timesheet with EmployeeTeamHistory using EmployeeID and date range logic.
Save the Dataflow and build your report in Service.
Use the merged AssignedTeam column in visuals.
Set up scheduled refresh so your weekly export updates automatically.
Example Outcome
Shania’s revenue up to 30/11/2025 → Team A.
Shania’s revenue from 01/12/2025 → Team B.
Bob stays in Team A the whole time.
Khris joins Team A on 01/12/2025 and contributes revenue from then.
11 Replies
- anilelmastasi
Super User
Hello selftaughtmike ,
You should change your Employee Dimension as Slowly Changing Dimension Type 2.
Your Employee ID is not unique but Employee ID + date is unique. So you can not create a relationship ResourceID->EmployeeID, because it is realted to time.
Your timesheet should be in relationship with calendar table.
You can use this pattern:
Revenue := SUMX ( Timesheet, VAR _emp = Timesheet[ResourceID] VAR _date = Timesheet[Date] RETURN CALCULATE ( SUM ( Timesheet[SalesAmount] ), FILTER ( Employee, Employee[EmployeeID] = _emp && Employee[StartDate] <= _date && ( ISBLANK ( Employee[EndDate] ) || Employee[EndDate] >= _date ) ) ) )If this solved your issue, please mark it as the accepted solution. ✅
Great, thank you anilelmastasi . I'm going to look at this and Jaywant-Thorat solution to see what works, so same question - is it possible to bring in different fields in to that revenue summary? So for example, how much Project ABC earned in a time period as opposed to how much a Team earned? Am assuming I can use the measure with other fields in a visual?
- anilelmastasi
Hi selftaughtmike ,
Yes you can use the same reveneue measure. It will work for Projects, Teams, Date etc. Because our modelling is how Slowly Changing Dimension 2 behave.
Timesheet and Calendar should be in relationship. But no direct relationship between Timesheet and Employee.
Other than that you can use it.
If this solved your issue, please mark it as the accepted solution. ✅
Hans-Georg_PulsHi selftaughtmike ,
as first step could be to setup a data model that fits better to your data and your requirements:
- Build a new employee table including only information about the person not about team or position
- Transform your existing employee table to a position (or whatever name your prefer) table including a new unique id for positions, including informations like team, start and end date and the ID of the employee
- Establish a 1:n relationship from employee table employee ID to the position table employee ID
Could that be an approach for you?
Thanks Hans-Georg_Puls
Is this what you're suggesting?Where you'd then have 1-to-many relationships:
Employees!Employee_ID 1:n Positions!Employee_ID
Employees!Employee_ID 1:n Timesheets!Resource_ID
Would there then need to be a relationship between the date fields in the new Positions table, with the Timesheets date so it knew what to map it to?
- Jaywant-Thorat
Hi selftaughtmike ,
This is a very real-world data modeling problem. You’ve described it perfectly.
Let me reassure you first:
- This is not a Power BI bug
- You are not supposed to force “unique EmployeeID” here
- This is a Slowly Changing Dimension
Let see what’s really going on:
- Your EmployeeID is unique per person,
- but NOT unique per business context over time.
So this assumption is wrong in this case:
“EmployeeID should be unique in the dimension”
Instead, the correct assumption is:
“EmployeeID + Date defines the employee’s attributes”
That’s why Power BI is pushing back.
CORRECT DATA MODEL
Use a Date-aware Employee History DimensionThis is the industry-standard solution.
Step 1: Employee Dimension (History Table)
Keep your Employee table as-is (this is GOOD data):
EmployeeID ==> FullName ==> Team ==> StartDate ==> EndDate
E0000002 ==> Shania Twain ==> A ==> 02/08/2024 ==> 30/11/2025
E0000002 ==> Shania Twain ==> B 01/12/2025 ==> (blank)Multiple rows per EmployeeID are expected and correct.
Step 2: Timesheet Fact Table (No Change Needed)
Your fact table is perfect:
- ResourceID
- Date
- SalesAmount
Do not try to force Team into the fact table.
Why a Normal Relationship Fails
Power BI relationships:
- Require 1 → Many
- Cannot resolve date-based attribute changes
So:
EmployeeID (not unique) → Timesheet
is Not allowed.
BEST PRACTICE: TREATAS + Date Logic
-----DAX-----
Create this measure:
Sales by Team :=
CALCULATE (
SUM ( Timesheet[SalesAmount] ),
FILTER (
Employees,
Employees[EmployeeID] = SELECTEDVALUE ( Timesheet[ResourceID] )
&& Timesheet[Date] >= Employees[StartDate]
&& Timesheet[Date] <= COALESCE ( Employees[EndDate], DATE ( 9999, 12, 31 ) )
)
)
-----DAX-----Result:
Sales on 29–30 Nov → Team A
Sales from 1 Dec onwards → Team B
Works perfectly in Team visuals
✔ Correct
✔ Time-aware
✔ No duplicate counting.=================================================
Did I answer your question? Mark my post as a solution! This will help others on the forum!Appreciate your Kudos!!
Jaywant Thorat | MCT | Data Analytics Coach
LinkedIn: https://www.linkedin.com/in/jaywantthorat/Join #MissionPowerBIBharat = https://shorturl.at/5ViW9
#MissionPowerBIBharat
LIVE with Jaywant Thorat from 15 Dec 2025
8 Days | 8 Sessions | 1 hr daily | 100% FreeAhh, thank you Jaywant-Thorat , at least I'm not going that mad!
Is it possible to bring in different fields in to that revenue summary? So for example, how much Project ABC earned in a time period as opposed to how much a Team earned? Am assuming I can use the measure with other fields in a visual?
- Olufemi7
Hello selftaughtmike,
TL;DR:
You can’t use EmployeeID alone as the unique key because employees move teams over time. The accepted solution is to model an Employee–Team history table with date ranges (a slowly changing dimension).
In Desktop, you handle this with relationships and DAX. In Service, you do it with Dataflows/Datamarts. Both approaches ensure historical revenue stays with the correct team.Option 1 – Power BI Desktop
If you’re modeling in Desktop, create an Employee–Team history table:
EmployeeID Team StartDate EndDate E0000002 A 02/08/2024 30/11/2025 E0000002 B 01/12/2025 (blank)Use (EmployeeID + Team + DateRange) as the unique key.
Relate Timesheet → EmployeeTeamHistory.
Add a calculated column in Timesheet:
AssignedTeam = LOOKUPVALUE( EmployeeTeamHistory[Team], EmployeeTeamHistory[EmployeeID], Timesheet[ResourceID], TRUE(), Timesheet[Date] >= EmployeeTeamHistory[StartDate] && (ISBLANK(EmployeeTeamHistory[EndDate]) || Timesheet[Date] <= EmployeeTeamHistory[EndDate]) )
This ensures Shania’s revenue up to 30/11/2025 is attributed to Team A, and from 01/12/2025 onward to Team B.Option 2 – Power BI Service (no Desktop)
If you’re working only in Service, use Dataflows or Datamarts:
Create a Dataflow with two entities:
Timesheet (revenue data).
EmployeeTeamHistory (employee table with StartDate/EndDate).
In Power Query (inside the Dataflow):
Add a composite key column:
EmployeeKey = EmployeeID & "-" & TeamMerge Timesheet with EmployeeTeamHistory using EmployeeID and date range logic.
Save the Dataflow and build your report in Service.
Use the merged AssignedTeam column in visuals.
Set up scheduled refresh so your weekly export updates automatically.
Example Outcome
Shania’s revenue up to 30/11/2025 → Team A.
Shania’s revenue from 01/12/2025 → Team B.
Bob stays in Team A the whole time.
Khris joins Team A on 01/12/2025 and contributes revenue from then.
v-hashadapuCommunity Support
Hi selftaughtmike , Thank you for reaching out to the Microsoft Community Forum.
We find the answer shared by anilelmastasi is appropriate. Can you please confirm if the solution worked for you. It will help others with similar issues find the answer easily.
Thank you anilelmastasi for your valuable response.
Will do, am going to look at all the suggestions below and see which one a) I can understand and b) then works. Will report back, but appreciate everyone's really helpful insights.
- v-hashadapu
Hi selftaughtmike , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.
