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)
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)
It's still not giving me multiple results
E.g
| Opportunity 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 result
| Resource 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_Geraldo1 year agoSuper 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.