Forum Discussion
Solved
Concatenate multiple rows with same ID that intersect the same period
I have a Project table with multiple tasks under the same project.
I have an Employee table with assignments per project.
How do I concatenate all employees that intersect the period of the start and end dates of each row in the Project Table?
Employee table:
| Employee Scheduled | Start Date | End Date | Project ID |
| John Doe | 4/3/2024 | 4/8/2024 | 100 |
| Victor Krum | 4/17/2024 | 4/25/2024 | 100 |
| John Sena | 3/20/2024 | 4/1/2024 | 100 |
| John Doe | 4/1/2024 | 4/12/2024 | 101 |
Project table:
| Project ID | Task Start Date | Task End Date | Expected Result |
| 100 | 4/1/2024 | 5/1/2024 | John Doe Viktor Krum John Sena |
| 100 | 4/15/2024 | 5/1/2024 | Viktor Krum |
| 101 | 4/12/2024 | 5/1/2024 | John Doe |
Try this calculated column in the Project table. It requires a date table 'Date'. No relationships exist among the three tables.
Employees = VAR vProjectID = Project[Project ID] VAR vTaskDates = DATESBETWEEN ( 'Date'[Date], Project[Task Start Date], Project[Task End Date] ) VAR vEmployeeTable = FILTER ( Employee, VAR vEmployeeDates = DATESBETWEEN ( 'Date'[Date], Employee[Start Date], Employee[End Date] ) RETURN Employee[Project ID] = vProjectID // at least one day in each date range must overlap && NOT ISEMPTY ( INTERSECT ( vTaskDates, vEmployeeDates ) ) ) VAR vResult = CONCATENATEX ( vEmployeeTable, Employee[Employee Scheduled] & UNICHAR ( 10 ) ) RETURN vResultHi,
This calculated column formula works
Column = CALCULATE(CONCATENATEX(VALUES(Employee[Employee Scheduled]),Employee[Employee Scheduled],", "),FILTER(Employee,Employee[Start Date]<=EARLIER(Project[Task End Date])&&Employee[End Date]>=EARLIER(Project[Task Start Date])&&Employee[Project ID]=EARLIER(Project[Project ID])))Hope this helps.
2 Replies
Try this calculated column in the Project table. It requires a date table 'Date'. No relationships exist among the three tables.
Employees = VAR vProjectID = Project[Project ID] VAR vTaskDates = DATESBETWEEN ( 'Date'[Date], Project[Task Start Date], Project[Task End Date] ) VAR vEmployeeTable = FILTER ( Employee, VAR vEmployeeDates = DATESBETWEEN ( 'Date'[Date], Employee[Start Date], Employee[End Date] ) RETURN Employee[Project ID] = vProjectID // at least one day in each date range must overlap && NOT ISEMPTY ( INTERSECT ( vTaskDates, vEmployeeDates ) ) ) VAR vResult = CONCATENATEX ( vEmployeeTable, Employee[Employee Scheduled] & UNICHAR ( 10 ) ) RETURN vResultHi,
This calculated column formula works
Column = CALCULATE(CONCATENATEX(VALUES(Employee[Employee Scheduled]),Employee[Employee Scheduled],", "),FILTER(Employee,Employee[Start Date]<=EARLIER(Project[Task End Date])&&Employee[End Date]>=EARLIER(Project[Task Start Date])&&Employee[Project ID]=EARLIER(Project[Project ID])))Hope this helps.