2 Tables Validation - HELP

[Anonymous]

The goal is to identify the "Owner" of the ticket metrics we have from an external source.  We have almost gotten this with the script below, but are still having trouble with many showing "unassigned" that should not.  We want to automate this report instead of manipulating this report and then forwarding it to Power BI.  This is what we are trying:

Table1 - 'IS Service - Data List'

[Home Team]                                       [Team: Name]

Apps Document Management            Apps Document Management                     

                                                            IDM & Messaging

Apps Tata NA Open Systems              

[Assignments]                                                                                              

Lewis Stanley, Syed Mashady, Team: Apps Document Management         

Team: IDM & Messaging

Abhishek Agarwal

[Assignments2]

Lewis Stanley, Syed Mashady, Team: Apps Document Management 

Team: IDM & Messaging

Abhishek Agarwal

Table 2 - 'Home Team Owners New'

[Hometeam]                                    [Owner]

Apps Document Management       Dale Durham

IDM & Messaging                           JR Foster

Apps Tata NA Open Systems          Surendrath Illathur

I am open to any suggestions, but the coding we may need some assistance with.

The Current Script:

Manager =
VAR HomeTeam1 =
CALCULATE (
FIRSTNONBLANK ( 'Home Team Owners New'[Owner], 1 ),
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'[Assignments2], '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 is greatly appreciated!

[v-shex-msft]

Hi @Anonymous,

Can you please share a sample pbix file for test? I think it will be help for coding formula.

Regards,

Xiaoxin Sheng

[Anonymous]

https://1drv.ms/u/s!AptW0NIBrqkuiFeE0sxzb3I-17vo

[Anonymous · Accepted solution]

I have resolved this by doing this for each value and then Conatenate them together.

RES1 = LOOKUPVALUE('Home Team Owners'[Owners],'Home Team Owners'[Hometeam],SELECTEDVALUE('IS Service Request  - Data List'[Home Team],"Unassigned"))

RES1 = LOOKUPVALUE('Home Team Owners'[Owners],'Home Team Owners'[Hometeam],SELECTEDVALUE('IS Service Request  - Data List'[Home Team],"Unassigned"))

RES3 = IF([RES2]=BLANK(),LOOKUPVALUE('Home Team Owners 4'[Owners],'Home Team Owners 4'[Hometeam],SELECTEDVALUE('IS Service Request  - Data List'[Assignments2],"Unassigned")))

RES4 = IF([RES3]=BLANK(),LOOKUPVALUE('Home Team Owners 4'[Owners],'Home Team Owners 4'[Hometeam],SELECTEDVALUE('IS Service Request  - Data List'[Assignments2],"Unassigned")))

RES1-2 = CONCATENATE([RES1], [RES2])

RES1-2 = CONCATENATE([RES1], [RES2])

ALL RES = CONCATENATE([RES1-2],[RES3-4])

Manager = if([ALL RES]=BLANK(),"Unassigned",[ALL RES])

This will also ensure that if there are any table changes then there will be no issues.

Thank you for those who attempted to assist.