Lookup a value within a table

[mark_carlisle]

I have a table in Power BI which looks similar to below (unable to post the actual table as this contains confidential information). Essentially it collates all of the different logins/user names from all the difference systems used into one table.

Employee No.	First Name	Surname	Full Name	Manager Name	Login 1	Login 2	Email	Team	Is Manager	ManagerEmail
1	Fahima	Maddox	Fahima Maddox	Jodi Vaughn	Fahima.Maddox	71328	Fahima.Maddox@example.com	Jodi Vaughn - Team 1	0
2	Jemima	Matthams	Jemima Matthams	Jemima Matthams	Jemima.Matthams	13576	Jemima.Matthams@example.com	Jemima Matthams - Team 2	1
3	Shantelle	Hopper	Shantelle Hopper	Jemima Matthams	Shantelle.Hopper	71313	Shantelle.Hopper@example.com	Jemima Matthams - Team 2	0
4	Catherine	Mahoney	Catherine Mahoney	Jodi Vaughn	Catherine.Mahoney	28973	Catherine.Mahoney@example.com	Jodi Vaughn - Team 1	0
5	Winston	Aguilar	Winston Aguilar	Jemima Matthams	Winston.Aguilar	26515	Winston.Aguilar@example.com	Jemima Matthams - Team 2	0
6	Jodi	Vaughn	Jodi Vaughn	Jodi Vaughn	Jodi.Vaughn	57630	Jodi.Vaughn@example.com	Jodi Vaughn - Team 1	1

What I want to be able to do is to lookup the email address of the employees manager and show it in the ManagerEmail column, as below.

Employee No.	First Name	Surname	Full Name	Manager Name	Login 1	Login 2	Email	Team	Is Manager	ManagerEmail
1	Fahima	Maddox	Fahima Maddox	Jodi Vaughn	Fahima.Maddox	71328	Fahima.Maddox@example.com	Jodi Vaughn - Team 1	0	Jodi.Vaughn@example.com
2	Jemima	Matthams	Jemima Matthams	Jemima Matthams	Jemima.Matthams	13576	Jemima.Matthams@example.com	Jemima Matthams - Team 2	1	Jemima.Matthams@example.com
3	Shantelle	Hopper	Shantelle Hopper	Jemima Matthams	Shantelle.Hopper	71313	Shantelle.Hopper@example.com	Jemima Matthams - Team 2	0	Jemima.Matthams@example.com
4	Catherine	Mahoney	Catherine Mahoney	Jodi Vaughn	Catherine.Mahoney	28973	Catherine.Mahoney@example.com	Jodi Vaughn - Team 1	0	Jodi.Vaughn@example.com
5	Winston	Aguilar	Winston Aguilar	Jemima Matthams	Winston.Aguilar	26515	Winston.Aguilar@example.com	Jemima Matthams - Team 2	0	Jemima.Matthams@example.com
6	Jodi	Vaughn	Jodi Vaughn	Jodi Vaughn	Jodi.Vaughn	57630	Jodi.Vaughn@example.com	Jodi Vaughn - Team 1	1	Jodi.Vaughn@example.com

I feel like this should be possible but I just can't think of what I would need to do.

[Zubair_Muhammad]

Hi @mark_carlisle

Hopefully this would work

Column =
VAR mytable =
    SUMMARIZE ( Table1, [Full Name], [Email] )
VAR mymanager = Table1[Manager Name]
RETURN
    LOOKUPVALUE ( [Email], [Full Name], mymanager )

[Zubair_Muhammad · Accepted solution]

@mark_carlisle

File attached as well

[mark_carlisle]

Thanks for this, however I get an error;

A table of multiple values was supplied where a single value was expected.

TeamManagerEmail = VAR mytable=SUMMARIZE('DV_STAFF2:DV_STAFFBY_EMPNO',[FullName],[SageEmailAddress])
VAR mymanager='DV_STAFF2:DV_STAFFBY_EMPNO'[Team Manager]
RETURN
LOOKUPVALUE([SageEmailAddress],[FullName],mymanager)

The only difference between mine and yours is the '' marks around the table name.

Any suggestions?

[v-chuncz-msft]

@mark_carlisle,

As mentioned here, if multiple rows match the search values and in all cases result_column values are identical then that value is returned. However, if result_column returns different values an error is returned.