Look up value based on filter from another table

[aaa222]

I have 3 tables

Each project has a project manager, project coordinator and admin assigned to it.

Table 1 shows only project manager information

Project Manager	Project ID
John Doe	12345
Jane Doe	78910

Table 2  shows all the emails for all the roles

Email

john.doe@123.com

jane.doe@123.com

Table 3 Has the user roles

Role ID	Role Description
PM	Project Manager
PC	Project Coordinator
AD	Admin

I need to extract the email addresses from Table 2 and need it to populate next to the project manager name in Table 1 and filter it by "Project Coordinator" from Table 3. 

So all the information are in separate tables.

the tables dont have any values that you can use to create relationships between them either. I also have to do this in DAX because we are using a live connection and all the power query options are diabled. 

Please help! 

[v-zhangti · Accepted solution]

Hi, @aaa222 

You can try the following methods.

Column:

Project Manager = LEFT([Email],4)&" "&RIGHT(LEFT([Email],8),3)

Column names cannot be quoted as data, so use text instead.

Role ID = 
LOOKUPVALUE('Table 3'[Role ID],'Table 3'[Role Description ],"Project Manager")

Email = 
LOOKUPVALUE('Table 2'[Email],'Table 2'[Project Manager],[Project Manager])

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aaa222]

I need the lookup to pull the project coordinators email address next to me PM name in the table.

[tamerj1]

Hi @aaa222 

what would be the expected results in this case? On which basis?