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 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!

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 OptionalSkill1 = MIN(opportunity[crb05_teamstructure.crb05_optionalskill1name])
//VAR OptionalSkill2 = MIN(opportunity[crb05_teamstructure.crb05_optionalskill2name])
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    

        )

// Resource Information
    VAR ResourceName = MAXX(MatchingResource, Capacity[Resource Name])
    VAR BenchStart = MINX(MatchingResource, Capacity[Bench Start Date])
    VAR ResourceRole = MAXX(MatchingResource, Capacity[Resource Role])
    VAR ResourcePrimarySkill = MAXX(MatchingResource, Capacity[Primary Skill])
    VAR ResourceSecondarySkill = MAXX(MatchingResource, Capacity[Secondary Skill])
    VAR ResourceSeniority = MAXX(MatchingResource, Capacity[Resource Seniority])

// Seniority Rating

    VAR RequiredSeniorityRating =
        SWITCH(
            TRUE(),
            RequiredSeniority = "Senior", 3,
            RequiredSeniority = "Mid-Level", 2,
            RequiredSeniority = "Junior", 1
        )
   
    VAR
        ResourceSeniorityRating =
            SWITCH(
                TRUE(),
                ResourceSeniority = "Senior", 3,
                ResourceSeniority = "Mid-Level", 2,
                ResourceSeniority = "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
        )

           

    RETURN
    IF(CompareSenioritiesRating = "Partial Match",
        IF(
            BenchStart <> BLANK(),    
       
                IF(
                    NOT ISBLANK(ResourceName),
                    ResourceName &
                    UNICHAR(10) &
                    ResourceRole &
                    UNICHAR(10) &
                    ResourcePrimarySkill &
                     UNICHAR(10) &
                    ResourceSecondarySkill &
                    UNICHAR(10) &
                    ResourceSeniority &
                    UNICHAR(10) &
                    CompareSenioritiesRating &
                    UNICHAR(10) &
                    " - BenchStart: "&
                    FORMAT(BenchStart, "dddd, mmmm dd, yyyy"),
                    "No Match"),

                IF(
                     NOT ISBLANK(ResourceName),
                                 ResourceName &
                                 UNICHAR(10) &
                                 ResourceRole &
                                 UNICHAR(10) &
                                 ResourcePrimarySkill &
                                 UNICHAR(10) &
                                  ResourceSecondarySkill &
                                 UNICHAR(10) &
                                 ResourceSeniority &
                                UNICHAR(10) &
                                CompareSenioritiesRating &
                                 UNICHAR(10) &
                                " - On Bench",
                     "No Match")
    ),
    "No Match"
    )
  • 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

  • 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
    )

  • 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's avatar
      dsj8wksnnckk
      Resolver I

      It's still not giving me multiple results

      E.g

      Opportunity 13Product DesignerFigmaJavaJenkinsFigmaBest CaseColdJunior


      Should have all these as match, but I only get the Resource 6 as a result

      Resource 22Product DesignerFigmaDockerMid-Level2025-03-14 00:00:00Partially Allocated
      Resource 24Product DesignerFigmaPythonMid-Level2025-03-01 00:00:00Partially Allocated
      Resource 6Product DesignerFigmaJavaJunior2025-02-04 00:00:00Partially Allocated
             
      • Bibiano_Geraldo's avatar
        Bibiano_Geraldo
        Super 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.