Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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:
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:
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!
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
9 | |
6 |