Forum Discussion
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 Patrick | 2 | 0 | 4 |
| Jenny Smith | 0 | 0 | 2 |
| Alex King | 1 | 1 | 0 |
| Total | 3 | 1 | 6 |
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 Patrick | 2 | 1 | 3 |
| Jenny Smith | 2 | 3 | 5 |
| Alex King | 2 | 6 | 8 |
The final result should look like this
| 'Resource'[UserName] | Total Tickets |
| Robert Patrick | 3 |
| Jenny Smith | 5 |
| Alex King | 8 |
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
- Ashish_Mathur
Super User
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.
- AnonymousNot applicable
Great question. The tags would indentify the second "Robert P." as "Robert P.2" and so forth for any additionals
- Ashish_Mathur
Super User
Trust me not even the most intelligent AI bot can guess that Robert P.2 is Robert Peter.