Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
I have resolved this by making multiple measures and the LOOKUPVALUE function with a Specific SELECTEDVALUE.
@Anonymous,
You may also try LOOKUPVALUE Function.
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
)
I have resolved this by making multiple measures and the LOOKUPVALUE function with a Specific SELECTEDVALUE.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |