Reply
Regular Visitor
Posts: 16
Registered: ‎04-04-2018
Accepted Solution

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

[ Edited ]

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!


Accepted Solutions
Highlighted
Regular Visitor
Posts: 16
Registered: ‎04-04-2018

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

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

View solution in original post


All Replies
Community Support Team
Posts: 2,510
Registered: ‎07-09-2016

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

@NickPatelMRCG,

 

You may also try LOOKUPVALUE Function.

Regards,
Sam
Regular Visitor
Posts: 16
Registered: ‎04-04-2018

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

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

    )

Highlighted
Regular Visitor
Posts: 16
Registered: ‎04-04-2018

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

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