Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Help requested with data/table modeling

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...
Need Help
  • ToddChitt's avatar
    ToddChitt
    3 years ago

    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.

Sahir_Maharaj's avatar
Sahir_Maharaj
Super User
3 years ago

Hello Anonymous,

 

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.