Forum Discussion
DAX mapping to return multiple values
Hi all,
I have this measure, but I can't seem to get multiple values as results.
Namely I have a list of Requirements for Opportunities (Index Column) and Capacity (Resource Name) - and I need a DAX measure to return all eligible pairs for Opportunity - Resource.
So far I get good results but only 1 per requirement. How can I ammend this DAX to give me multiple results?
Note: Power Query is not an option because the tables are calculated
Thanks in advance!
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)
4 Replies
- bhanu_gautamSuper User
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
) - Bibiano_GeraldoSuper User
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)- dsj8wksnnckkResolver I
It's still not giving me multiple results
E.gOpportunity 13 Product Designer Figma Java Jenkins Figma Best Case Cold Junior
Should have all these as match, but I only get the Resource 6 as a resultResource 22 Product Designer Figma Docker Mid-Level 2025-03-14 00:00:00 Partially Allocated Resource 24 Product Designer Figma Python Mid-Level 2025-03-01 00:00:00 Partially Allocated Resource 6 Product Designer Figma Java Junior 2025-02-04 00:00:00 Partially Allocated - Bibiano_GeraldoSuper User
Hi dsj8wksnnckk ,
In DAX, a measure is designed to return a single scalar value. This means you cannot have a measure that outputs multiple rows or separate values directly. However, you can combine multiple pieces of information into a single text string (for example, by concatenating them using functions like CONCATENATEX). This way, even though you’re displaying multiple values, they are all returned together in one line as one scalar value.