topic Re: Mapping columns from two tables in DAX Commands and Tips

Mapping columns from two tables

dsj8wksnnckk — Thu, 14 Nov 2024 11:52:29 GMT

Hi, I need help with DAX.

I have these 2 tables

Table1

Opportunity ID	Opportunity Stage	Role	Skill	Seniority
Opportunity A	Committed	Software Developer	.NET	Senior
Opportunity A	Committed	Software Developer	Java	Junior
Opportunity A	Committed	Product Designer	Design	Junior
Opportunity B	Proposed	Data Scientist	Python	Medior
Opportunity B	Proposed	Frontend Developer	React	Senior
Opportunity B	Proposed	Business Analyst	Requirements	Junior
Opportunity C	In Negotiation	Backend Developer	Node.js	Medior
Opportunity C	In Negotiation	UI/UX Designer	Figma	Senior
Opportunity C	In Negotiation	Scrum Master	Agile	Junior
Opportunity D	Committed	Software Developer	PHP	Medior
Opportunity D	Committed	Data Engineer	Spark	Senior
Opportunity D	Committed	DevOps Engineer	Docker	Medior
Opportunity E	Proposed	Cybersecurity Analyst	Network Security	Junior
Opportunity E	Proposed	Cloud Engineer	AWS	Senior
Opportunity F	Committed	Full Stack Developer	MEAN	Medior
Opportunity F	Committed	QA Engineer	Selenium	Senior
Opportunity G	In Negotiation	System Administrator	Linux	Junior
Opportunity G	In Negotiation	Product Manager	Roadmaps	Senior
Opportunity G	In Negotiation	Machine Learning Engineer	TensorFlow	Medior
Opportunity H	Committed	AI Engineer	NLP	Junior
Opportunity H	Proposed	Cloud Architect	Azure	Senior
Opportunity H	Proposed	Database Administrator	MySQL	Medior
Opportunity I	In Negotiation	Software Engineer	C++	Junior
Opportunity I	In Negotiation	Marketing Specialist	SEO	Medior
Opportunity J	Proposed	Mobile Developer	iOS	Senior
Opportunity K	Committed	Data Analyst	SQL	Medior
Opportunity K	Committed	Frontend Developer	Vue.js	Senior

Table2

Resource Name	Role	Skill	Seniority	Commercial Status	Bench Start	Bench End
Rick	Software Developer	.NET	Senior	In Delivery
Morty	Software Developer	Java	Medior	On Bench	01.01.2025	15.04.2025
Dean	Product Designer	Design	Medior	On Bench	01.01.2025	01.03.2025
Sam	SDM	Project Management	N/A	In Delivery
Lucifer	Software Developer	.NET	Senior	On Bench	01.01.2025	01.07.2025
Jane	Data Scientist	Python	Senior	In Delivery
Alice	Data Analyst	SQL	Junior	On Bench	15.02.2025	01.05.2025
Mike	Frontend Developer	React	Senior	On Bench	10.01.2025	30.03.2025
Kate	UI/UX Designer	Figma	Medior	On Bench	05.02.2025	05.04.2025
George	Cloud Engineer	AWS	Senior	On Bench	01.03.2025	01.06.2025
Noah	System Administrator	Linux	Junior	On Bench	20.02.2025	15.04.2025
Sophia	Data Engineer	Spark	Medior	On Bench	15.01.2025	01.03.2025
Liam	Cybersecurity Analyst	Network Security	Junior	On Bench	10.03.2025	30.05.2025
Ella	Full Stack Developer	MEAN	Medior	On Bench	01.02.2025	15.05.2025
Ethan	AI Engineer	NLP	Medior	On Bench	05.04.2025	10.06.2025
Natalie	QA Lead	Manual Testing	Senior	On Bench	15.01.2025	01.03.2025
Liam	Frontend Developer	Vue.js	Junior	On Bench	01.03.2025	01.07.2025
Zach	Software Engineer	C++	Senior	On Bench	20.02.2025	01.04.2025
Isla	Mobile Developer	iOS	Medior	On Bench	15.03.2025	01.07.2025
Mia	Product Manager	Roadmaps	Roadmaps	Senior	In Delivery
Ella	Full Stack Developer	MEAN	MEAN	Medior	On Bench	01.02.2025
Ava	ML Engineer	TensorFlow	TensorFlow	Senior	In Delivery
Ethan	AI Engineer	NLP	MySQL	Medior	On Bench	05.04.2025
Jackson	Cloud Architect	Azure	Azure	Senior	In Delivery
Natalie	QA Lead	Manual Testing	Manual Testing	Senior	On Bench	15.01.2025
Liam	Frontend Developer	Vue.js	Vue.js	Junior	On Bench	01.03.2025
Hannah	Database Administrator	MySQL	iOS	Medior	In Delivery
Zach	Software Engineer	C++	C++	Senior	On Bench	20.02.2025
Isla	Mobile Developer	iOS	iOS	Medior	On Bench	15.03.2025
Grace	Marketing Specialist	SEO	SEO	Junior	In Delivery
			SEO

I need to create a calculated column in Table1:

For every Commited Opportunity find a match in Role and Skill from Table2 but only Resource that is on Bench

Return the Resource name as a result in the new calculated column (if possible Bench start and end date as well)

Thanks in advance

Re: Mapping columns from two tables

123abc — Thu, 14 Nov 2024 12:17:13 GMT

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.

Re: Mapping columns from two tables

dsj8wksnnckk — Thu, 14 Nov 2024 13:02:26 GMT

Hi,

thanks for your help. I have adjusted the formula and got the result I needed

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 = MAXX(MatchingResource, Table2[Resource Name])
VAR BenchStart = MAXX(MatchingResource, Table2[Bench Start])
VAR BenchEnd = MAXX(MatchingResource, Table2[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"
)