Forum Discussion

dsj8wksnnckk's avatar
dsj8wksnnckk
Resolver I
1 year ago
Solved

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 ...
  • Bibiano_Geraldo's avatar
    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)