Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Need to count string and total with string in another table

Hello, 

 

I am working on a report with many sources and am having trouble totaling the number of tickets a person is assigned and assists with. 

One of the main relationship in the report is from my table 'Resource' and column 'UserName' which holds a persons First and Last Name (Jenny Smith)
The source with the data is the table 'SupportTickets' and using the columns [Assigned to] and [Tags] 

 

If a name is in the [Tags] then that means they assisted the person who was assigned the ticket.
The columns with [Tags] contains the first name and last initial of any user within the 'Resource'[UserName].

Difference in name display example: 

'Resource'[UserName] = Jenny Smith

'SupportTickets'[Tags] = Jenny S.

 

What i would expect counting the sum of 'Tags' to look like if it were in a table (numbers are just for the example)

'SupportTickets'[Tags] →
'Resource'[UserName] ↓
Jenny S     Robert P.    Alex K.    
Robert Patrick204
Jenny Smith002
Alex King110
Total316

 

 

The next step would be to combine the two columns

'Resource'[UserName]    Count of 'SupportTickets'[Assigned To]    Sum of Assists ( 'Support Tickets'[Tags])    Total Tickets    
Robert Patrick213
Jenny Smith235
Alex King268

 

The final result should look like this

'Resource'[UserName]      Total Tickets    
Robert Patrick3
Jenny Smith5
Alex King8

 

 

I am still fairly new to Powerbi, to that i mean i only have basic experience creating and useing DAX measures. If possible i would like to stick to that method.

 

Thank you in advance.

3 Replies

  • Hi,

    If there are 2 people = Robert Patrick and Robert Peter, then how would one be abl to differentiate them in the Tags column - both would be Robert P.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Great question. The tags would indentify the second "Robert P." as "Robert P.2" and so forth for any additionals

  • Trust me not even the most intelligent AI bot can guess that Robert P.2 is Robert Peter.