Forum Discussion
Returning values from a many to many relationship
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.
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 _resultit worked like:
2 Replies
FreemanZSuper User
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 _resultit worked like:
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!
