DAX 2 Tables, 3 Columns Raw Data, 2 Columns with Answers. Need One Column with only 1 Answer.

[Anonymous]

I have 5 Columns in all. 2 Tables. the First 3 Columns have similar data. The Second Table and other 2 columns have the answers. the goal is to match the first 3 columns (One at a time) and compare them to the 1st column in the second table. Then the 2nd column data in the 2nd table is the answer. If the name in the 1st column of the 2nd table matches the first (through the 3rd) in the first table (See Below).  I need to make a single column in the Raw data Report to be generated wiht the owner in each row identified or be labled as "Unassigned". I have gotten close. Please see below.

Table 1 (Raw Data) - IS Service Request  - Data List

Team: Name      Assignments      Assignments_1

Team 1                                          Team 1

                          Team 2                Team 2

                                                      Team 3

Table 2 (Answer Table) - Home Team Owners New

Hometeam (Team Name Match)     Owner (Answers)

Team 1                                             Jamie

Team 2                                             Mike

Team 3                                             Alex

Dax:

Managers =

VAR HomeTeam1 =

    CALCULATE (

        FIRSTNONBLANK ( 'Home Team Owners New'[Owner], 1 ),

        USERELATIONSHIP( 'Home Team Owners New'[Owner], 'Home Team Owners New'[Hometeam])

        FILTER (

            'Home Team Owners New',

            'Home Team Owners New'[Hometeam]

                = MAX ( 'IS Service Request  - Data List'[Home Team] )

        )

    )

VAR HomeTeam2 =

    CALCULATE (

        FIRSTNONBLANK ( 'Home Team Owners New'[Owner], 1 ),

        USERELATIONSHIP ( 'IS Service Request  - Data List'[Team: Name], 'Home Team Owners New'[Hometeam] ),

        FILTER (

            'Home Team Owners New',

            'Home Team Owners New'[Hometeam]

                = MAX ( 'IS Service Request  - Data List'[Team: Name] )

        )

    )

VAR assignment1 =

    CALCULATE (

        FIRSTNONBLANK ( 'Home Team Owners New'[Owner], 1 ),

        USERELATIONSHIP ( 'IS Service Request  - Data List'[Assignments], 'Home Team Owners New'[Hometeam] ),

        FILTER (

            'Home Team Owners New',

            'Home Team Owners New'[Hometeam]

                = MAX ( 'IS Service Request  - Data List'[Assignments] )

        )

    )

VAR assignment2 =

    CALCULATE (

        FIRSTNONBLANK ( 'Home Team Owners New'[Owner], 1 ),

        USERELATIONSHIP ( 'IS Service Request  - Data List'[Assignments_1], 'Home Team Owners New'[Hometeam] ),

        FILTER (

            'Home Team Owners New',

            'Home Team Owners New'[Hometeam]

                = MAX ( 'IS Service Request  - Data List'[Assignments] )

        )

    )

RETURN

    IF (

        HomeTeam1 = BLANK (),

        IF (

            HomeTeam2 = BLANK (),

            IF (

                assignment1 = BLANK (),

                IF ( assignment2 = BLANK (), "Unassigned", assignment2 ),

                assignment1

            ),

            HomeTeam2

        ),

        HomeTeam1

    )

    )

Any Help on this would be greatly appreciated!

[v-chuncz-msft]

@Anonymous,

You may also try LOOKUPVALUE Function.

[Anonymous]

When attempted I get an error stating that it cannot do a true or false result.

This is what I have. What do you suggest?

It is not returning the content in the Owner column correctly which is our ultimate issue. We get multiple as "Unassigned and only a few out of over 2000 lines assigned to an actual owner.

VARIABLES =

VAR HomeTeam1 =

    CALCULATE (

        FIRSTNONBLANK ( 'Home Team Owners New'[Owner], 1 ),

         Lookupvalue( 'Home Team Owners New'[Owner], 'Home Team Owners New'[Hometeam], 'IS Service Request - Data List'[Home Team]) )

        )

    )

VAR HomeTeam2 =

    CALCULATE (

        FIRSTNONBLANK ( 'Home Team Owners New'[Owner], 1 ),

        USERELATIONSHIP ( 'IS Service Request  - Data List'[Team: Name], 'Home Team Owners New'[Hometeam] ),

         Lookupvalue( 'Home Team Owners New'[Owner], 'Home Team Owners New'[Hometeam], 'IS Service Request - Data List'[Team: Name]) )

        )

    )

VAR assignment1 =

    CALCULATE (

        FIRSTNONBLANK ( 'Home Team Owners New'[Owner], 1 ),

        USERELATIONSHIP ( 'IS Service Request  - Data List'[Assignments], 'Home Team Owners New'[Hometeam] ),

        Lookupvalue( 'Home Team Owners New'[Owner], 'Home Team Owners New'[Hometeam], 'IS Service Request - Data List'[Assignments]) )

        )

    )

VAR assignment2 =

    CALCULATE (

        FIRSTNONBLANK ( 'Home Team Owners New'[Owner], 1 ),

        USERELATIONSHIP ( 'IS Service Request  - Data List'[Assignments2], 'Home Team Owners New'[Hometeam] ),

       Lookupvalue( 'Home Team Owners New'[Owner], 'Home Team Owners New'[Hometeam], 'IS Service Request - Data List'[Assignments2])       )

    )

RETURN

    IF (

        HomeTeam1 = BLANK (),

        IF (

            HomeTeam2 = BLANK (),

            IF (

                assignment1 = BLANK (),

                IF ( assignment2 = BLANK (), "Unassigned", assignment2 ),

                assignment1

            ),

            HomeTeam2

        ),

        HomeTeam1

    )

[Anonymous · Accepted solution]

I have resolved this by making multiple measures and the LOOKUPVALUE function with a Specific SELECTEDVALUE.