Returning values from a many to many relationship

[markgsmith01]

I have an event table and an employee table that share a many to many relationship. I want to to polulate the role that an employee had at the time of the event. For example, the first event occurred to "Bob" on 1/4/2020, and I can see in the Employee table that  he was an Analyst at that date. But he was a consultant when the second event that involved "Bob" occurred.

Event Table

Date	Name
1/04/2020	Bob
1/07/2020	Bob
3/05/2021	Jill
13/02/2022	Fred

Employee Table

Name	Role	Start Date	End Date
Bob	Analyst	1/01/2020	1/06/2020
Bob	Consultant	2/06/2020	1/01/2023
Jill	Consultant	15/07/2018	3/12/2020
Jill	Trainer	4/12/2020	1/01/2023
Fred	Analyst	1/05/2021	1/01/2023

Desired Result Event Table

Date	Name	Role
1/04/2020	Bob	Analyst
1/07/2020	Bob	Consultant
3/05/2021	Jill	Trainer
13/02/2022	Fred	Analyst

I've been banging my head against the wall for a while on this but I figure it shouldn't be that hard!?! Any help would be appreciated.

[FreemanZ · Accepted solution]

hi @markgsmith01 ,

try to add a calculated column in event table like:

column = 
VAR _name =[name]
VAR _date = [date]
VAR _result =
MAXX(
    FILTER(
        employee,
        employee[Name]=_name
            &&employee[Start Date]<=_date
            &&employee[End Date]>=_date
    ),
    employee[role]
)
RETURN _result

it worked like:

[markgsmith01]

Thanks @FreemanZ . That's exactly what I want. This sort of scenario keeps coming up for me. The formula seems simple but my brain seems to take a bit to absorb it!