Help requested with data/table modeling

[Douttful]

Sorry for the genaric subject line, but I don't know what else to call this. 

I work in a call center and use Power BI reports to calcualte metrics for analysts within the team. I am connected to a Teradata table that pulls information nightly from our ticketing system. The data is extracted 1 to 1 and is not something I can edit (easily)

In the teradata table there is default information for each ticket submitted, everything you can imagine. Submit date, who submitted the incident, what team fixed it, who on that team resolved the incident etc. The main columns I'm struggling with currently are: 

Submitter: Employee ID number of the team member who submitted the incident

Assignee: Text name of the team member who resovled the incident.

Here is the rub. Each employee has an ID, but our ticketing system has had multiple different names listed for team members. Example:

Name: Joe Smith

EMPLID: 1234567

Assignee Name: Joseph Smith, Joe Smith, Joe N Smith

Technically I could do find replace upon import but that sounds like a MONSTER for refresh processing and based on my research that's a bad thing to do (this table has hundreds of thousands of rows and will continue to grow)  

I need to calculate when the submitter IS the assignee (IE my team member resolved the issue) but the statements I've been trying are NOT working and I'm at my whits end.

Below is the code I've got so far in two calculated columns. 

TO DETERMINE IF A TICKET WAS RESOLVED BY MY TEAM: 

L1Resolved =

Var _HelixName = (LOOKUPVALUE('TM Active Data'[Helix Full Name], 'TM Active Data'[EMPLID Text], 'Live L1 Tickets'[INCD_SBMT_ID]))

Var _RemedyName = (LOOKUPVALUE('TM Active Data'[Remedy 9 Name], 'TM Active Data'[EMPLID Text], 'Live L1 Tickets'[INCD_SBMT_ID]))

Var _fullname = (LOOKUPVALUE('TM Active Data'[Full Name], 'TM Active Data'[EMPLID Text], 'Live L1 Tickets'[INCD_SBMT_ID]))

Var Helix_L1Name = IF(CONTAINSSTRING('Live L1 Tickets'[ASSIGNEE], _HelixName), "Yes", "No")

Var Remedy_L1Name = IF(CONTAINSSTRING('Live L1 Tickets'[ASSIGNEE], _RemedyName), "Yes", "No")

Var Full_L1Name = IF(CONTAINSSTRING('Live L1 Tickets'[ASSIGNEE], _fullname), "Yes", "No")

Var _L1Resolve = IF(

    OR(

        OR((LOOKUPVALUE('TM Active Data'[Helix Full Name], 'TM Active Data'[EMPLID Text], 'Live L1 Tickets'[INCD_SBMT_ID])) = "Yes", (LOOKUPVALUE('TM Active Data'[Remedy 9 Name], 'TM Active Data'[EMPLID Text], 'Live L1 Tickets'[INCD_SBMT_ID])) = "Yes"), (LOOKUPVALUE('TM Active Data'[Full Name], 'TM Active Data'[EMPLID Text], 'Live L1 Tickets'[INCD_SBMT_ID])) = "Yes"), True, False)

RETURN (

    _L1Resolve)

To determine time from Submitt date to last resolved date IF the ticket was resolved by my team: 

L1TimetoResolve = IF('Live L1 Tickets'[L1Resolved]= FALSE(), INT(DATEDIFF('Live L1 Tickets'[Submit_date_time], 'Live L1 Tickets'[Last_Resolved_date_time],HOUR)), BLANK())

There are two tables involved currently, TM Active Data: Data for all my team members, and Live L1 Tickets: Exported ticket detail in Teradata table. 

Would making a new table with EMPLID and all the possible names be a better way forward? Or does anyone have any better ideas. 

Thank you so much for your time. 

[Sahir_Maharaj]

Hello @Douttful,

Create a calculated column in the "Live L1 Tickets" table to extract the employee ID from the "Assignee" column:

Assignee ID = 
VAR AssigneeNames = {"Joseph Smith", "Joe Smith", "Joe N Smith"}
VAR Assignee = 'Live L1 Tickets'[Assignee]
VAR Matches = FILTER(AssigneeNames, CONTAINSSTRING(Assignee, ))
RETURN IF(ISBLANK(Matches), "", "1234567") // replace "1234567" with the actual EMPLID for this assignee

Create another calculated column in the "Live L1 Tickets" table to check if the ticket was resolved by your team:

Resolved by L1 = IF('Live L1 Tickets'[Submitter] = 'Live L1 Tickets'[Assignee ID], TRUE(), FALSE())

Calculate the time from the submit date to the last resolved date if the ticket was resolved by your team:

Time to Resolve = 
IF(
    NOT('Live L1 Tickets'[Resolved by L1]),
    BLANK(),
    DATEDIFF(
        MIN('Live L1 Tickets'[Submit_date_time]),
        MAXX(
            FILTER('Live L1 Tickets', 'Live L1 Tickets'[Resolved by L1]),
            'Live L1 Tickets'[Last_Resolved_date_time]
        ),
        HOUR
    )
)

Let me know if this works for you.

[ToddChitt · Accepted solution]

I suggest you start building a seperate Employee Dimension table, or set of tables. Table1: Employee ID and Employee Name, where ID is the unique key. Table2: Employee Name variations with [Proper Name] as one column and [Name as someone typed into the system because there is obviously no validation going on] as the second.

Table 1:

ID       Name

==     ====

1234    John Smith

Table 2:

Name                other names

====                =========

John Smith        John Q Smith

John Smith        Smith, John

John Smith       Smith, John Q

Then after you get frustrated with having to manage this list as you continually find more variations, go to the people that actually enter the data and tell them to adopt some standards when adding people's names.

[Douttful]

I laughed out loud! Thanks so much! This helps a ton.