You can create a calculated column in Table1 that checks for matching roles and skills in Table2 for committed opportunities....PLZ TRY BELOW MEASURE.
Assigned Resource =
VAR CurrentOpportunity = Table1[Opportunity ID]
VAR CurrentRole = Table1[Role]
VAR CurrentSkill = Table1[Skill]
VAR CurrentStage = Table1[Opportunity Stage]
RETURN
IF(
CurrentStage = "Committed",
VAR MatchingResource =
FILTER(
Table2,
Table2[Role] = CurrentRole &&
Table2[Skill] = CurrentSkill &&
Table2[Commercial Status] = "On Bench"
)
VAR ResourceName = SELECTEDVALUE(MatchingResource[Resource Name])
VAR BenchStart = SELECTEDVALUE(MatchingResource[Bench Start])
VAR BenchEnd = SELECTEDVALUE(MatchingResource[Bench End])
RETURN
IF(NOT ISBLANK(ResourceName),
ResourceName & " (Bench Start: " & FORMAT(BenchStart, "dd.MM.yyyy") &
", Bench End: " & FORMAT(BenchEnd, "dd.MM.yyyy") & ")",
"No Match"
),
"Not Committed"
)
RESULT:
- If a match is found, the result shows the Resource Name and their bench start and end dates.
- If there’s no match, it returns "No Match".
- If the opportunity stage is not "Committed," it returns "Not Committed."
This formula will give you a calculated column in Table1 with the assigned resource's name, bench start, and end dates for each committed opportunity.
