Repeating "unique" employee IDs

[selftaughtmike]

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

[anilelmastasi · Accepted solution]

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. ✅

[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.

[v-hashadapu]

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.

[selftaughtmike]

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.

[Olufemi7 · 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:

1. Create a Dataflow with two entities:

   • Timesheet (revenue data).

   • EmployeeTeamHistory (employee table with StartDate/EndDate).

2. In Power Query (inside the Dataflow):

   • Add a composite key column:
     
     

EmployeeKey = EmployeeID & "-" & Team

• Merge 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.
      
      
      
      

[anilelmastasi]

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. ✅

[selftaughtmike]

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 · Accepted solution]

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. ✅

[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 Dimension

This 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% Free

[selftaughtmike]

Ahh, 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?

[Hans-Georg_Puls]

Hi @selftaughtmike ,

as first step could be to setup a data model that fits better to your data and your requirements:

1. Build a new employee table including only information about the person not about team or position
2. 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
3. Establish a 1:n relationship from employee table employee ID to the position table employee ID

Could that be an approach for you?

[selftaughtmike]

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?