Forum Discussion
DAX 2 Tables, 3 Columns Raw Data, 2 Columns with Answers. Need One Column with only 1 Answer.
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!
3 Replies
v-chuncz-msftCommunity Support
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
)