I have 2 tables that are associated with the ID column. One table has project data and the other table has the users associated with the project. Each project has a status which indicates who has current ownership of the project. I would like a way to show in the table everyone in a certain role associated with the project based on the status. There can be multiple people in the same role, so I would need multiple people to show in one field delimited by a comma. I would like the field to be searchable. I created a Pick Match measure in the table to get the names delimited by commas, but it was not searchable (thanks to Shubham_singh for the solution!). I was told to use the data load editor, but I am not sure how to implement Pick Match in there.
Table 1
| ID | Project | Date Started | Status |
| 1 | Project1 | 2019-03-15 | Initiated |
| 2 | Project2 | 2018-08-23 | Reviewed |
| 3 | Project3 | 2019-02-01 | Final |
Table 2
| ID | User Role | User | E-mail |
| 1 | Admin | User1 | user1@user1.com |
| 1 | Project Manager | User2 | user2@user2.com |
| 1 | Analyst | User3 | user3@user3.com |
| 1 | Analyst | User5 | user5@user5.com |
| 2 | Project Manager | User2 | user2@user2.com |
| 2 | Analyst | User4 | user4@user4.com |
| 3 | Project Manager | User6 | user6@user6.com |
| 3 | Analyst | User3 | user3@user3.com |
| 3 | Admin | User7 | user7@user7.com |
Desired Result
| ID | Project | Date Started | Status | User | E-mail |
| 1 | Project1 | 2019-03-15 | Initiated | User3, User5 | user3@user3.com, user5@user5.com |
| 2 | Project2 | 2018-08-23 | Reviewed | (Missing) | (Missing) |
| 3 | Project3 | 2019-02-01 | Final | User6 | user6@user6.com |
Logic
| If Status = Initiated, show Analyst |
| If Status = Reviewed, show Admin |
| If Status = Final, show Project Manager |
If Status = Initiated and there is no Analyst associated with the project, fill in the field with "(Missing)". This is an error catch for me.
Any guidance would be greatly appreciated.
