Forum Discussion
Relation problem
I've got 2 tables ActivityID Date EmpID EmpName 1 19-8-2020 3 Jansen 2 20-8-2020 4 de Boer 3 21-8-2020 5 Fransen EmpID EmpName Function From To 3 Ja...
Hi Anonymous ,
Would you please try the calculated column below:
Function = CALCULATE ( CONCATENATEX ( VALUES ( Table2[Function] ), Table2[Function], "," ), FILTER ( Table2, Table2[From] <= EARLIER ( 'Table'[Date] ) && COALESCE ( Table2[To], TODAY () ) >= EARLIER ( 'Table'[Date] ) && Table2[EmpID] = EARLIER ( 'Table'[EmpID] ) ) )If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi Anonymous ,
Would you please try the calculated column below:
Function =
CALCULATE (
CONCATENATEX ( VALUES ( Table2[Function] ), Table2[Function], "," ),
FILTER (
Table2,
Table2[From] <= EARLIER ( 'Table'[Date] )
&& COALESCE ( Table2[To], TODAY () ) >= EARLIER ( 'Table'[Date] )
&& Table2[EmpID] = EARLIER ( 'Table'[EmpID] )
)
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
I tried your formula and I got some good answers but not everything is correct.
| ActivityID | Date | EmpID | EmpName | Function |
| 1 | 19-8-2020 | 3 | Jansen | GZ-psycholoog |
| 2 | 20-8-2020 | 4 | de Boer | Schoonmaker |
| 3 | 21-8-2020 | 5 | Fransen |
I expected Controller in activity 3. Do you know what went wrong?
Edit: The issue was because the current date (20-8-2020) was before the date of the activity. I've changed the dates in the tables and everything worked, thanks!!!