Forum Discussion
DAX mapping to return multiple values
- 1 year ago
Hi dsj8wksnnckk ,
To return multiple values, you need to concatenate the matching resource names instead of using MAXX, please try modifying your measure like this:Measure Capacity to Pipeline Partial Match = VAR CurrentOpportunity = SELECTEDVALUE(Opportunities[Index]) VAR RequiredRole = MIN(Opportunities[Required Role]) VAR RequiredSkill1 = MIN(Opportunities[Required Skill1]) VAR RequiredSkill2 = MIN(Opportunities[Required Skill2]) VAR Forecast = MIN(Opportunities[Opportunity Forecast]) VAR RequiredSeniority = MIN(Opportunities[Required Seniority]) VAR MatchingResource = FILTER( Capacity, Capacity[Resource Role] = RequiredRole && (RequiredSkill1 = Capacity[Primary Skill] || RequiredSkill1 = Capacity[Secondary Skill]) && (RequiredSkill2 = Capacity[Primary Skill] || RequiredSkill2 = Capacity[Secondary Skill]) ) VAR RequiredSeniorityRating = SWITCH( TRUE(), RequiredSeniority = "Senior", 3, RequiredSeniority = "Mid-Level", 2, RequiredSeniority = "Junior", 1 ) VAR ResourceTable = ADDCOLUMNS( MatchingResource, "ResourceSeniorityRating", SWITCH( TRUE(), Capacity[Resource Seniority] = "Senior", 3, Capacity[Resource Seniority] = "Mid-Level", 2, Capacity[Resource Seniority] = "Junior", 1 ) ) VAR FilteredResources = FILTER( ResourceTable, RequiredSeniorityRating - [ResourceSeniorityRating] <= 0 ) VAR Result = CONCATENATEX( FilteredResources, Capacity[Resource Name] & " - " & Capacity[Resource Role] & " - " & Capacity[Primary Skill] & " - " & Capacity[Secondary Skill] & " - " & Capacity[Resource Seniority] & " - BenchStart: " & FORMAT(Capacity[Bench Start Date], "dddd, mmmm dd, yyyy"), UNICHAR(10) ) RETURN IF(ISBLANK(Result), "No Match", Result)
dsj8wksnnckk , Try using
Measure Capacity to Pipeline Partial Match =
VAR CurrentOpportunity = SELECTEDVALUE(Opportunities[Index])
VAR RequiredRole = MIN(Opportunities[Required Role])
VAR RequiredSkill1 = MIN(Opportunities[Required Skill1])
VAR RequiredSkill2 = MIN(Opportunities[Required Skill2])
VAR Forecast = MIN(Opportunities[Opportunity Forecast])
VAR RequiredSeniority = MIN(Opportunities[Required Seniority])
RETURN
VAR MatchingResource =
FILTER(
Capacity,
Capacity[Resource Role] = RequiredRole && // Role match 100%
(RequiredSkill1 = Capacity[Primary Skill]
||
RequiredSkill1 = Capacity[Secondary Skill]) // RequiredSkill1 match either Primary or Secondary
&&
(RequiredSkill2 = Capacity[Primary Skill]
||
RequiredSkill2 = Capacity[Secondary Skill]) // RequiredSkill2 match either Primary or Secondary
)
VAR RequiredSeniorityRating =
SWITCH(
TRUE(),
RequiredSeniority = "Senior", 3,
RequiredSeniority = "Mid-Level", 2,
RequiredSeniority = "Junior", 1
)
VAR ResourceSeniorityRating =
SWITCH(
TRUE(),
Capacity[Resource Seniority] = "Senior", 3,
Capacity[Resource Seniority] = "Mid-Level", 2,
Capacity[Resource Seniority] = "Junior", 1
)
VAR CompareSenioritiesRating =
SWITCH(
TRUE(),
RequiredSeniorityRating - ResourceSeniorityRating = 0, "Match", // Matching Seniority
RequiredSeniorityRating - ResourceSeniorityRating < 0, "Match", // RequiredSeniority is lower
RequiredSeniorityRating - ResourceSeniorityRating > 0, "Partial match" // Need to add the No Match to the last IF function - they need to be disregarded
)
VAR Result =
CONCATENATEX(
FILTER(
MatchingResource,
CompareSenioritiesRating = "Partial Match"
),
Capacity[Resource Name] & " - " &
Capacity[Resource Role] & " - " &
Capacity[Primary Skill] & " - " &
Capacity[Secondary Skill] & " - " &
Capacity[Resource Seniority] & " - " &
"BenchStart: " & FORMAT(Capacity[Bench Start Date], "dddd, mmmm dd, yyyy"),
UNICHAR(10)
)
RETURN
IF(
ISBLANK(Result),
"No Match",
Result
)