Repeating "unique" employee IDs

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:

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.