DAX mapping to return multiple values

[dsj8wksnnckk]

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"

    )

[Bibiano_Geraldo · Accepted solution]

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]

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_Geraldo]

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.

[bhanu_gautam]

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