Power BI - Lookup within existing table to add new columns for missing manager information

[ShaneHi]

Hi,

I'm looking for some support in creating additional column(s) which lookup within the same table.

I've attached a similar table below where my data currently runs from Columns A - D. I would like Columns E-H to be created looking up the data from the existing table.

Is this achievable  and how would I do it?

A	B	C	D	E	F	G	H
UserID	UserName	ManagerID	ManagerName	(new column) Manager2ID	(new column) Manager2Name	(new column) Manager2ID	(new column) Manager2Name
1	Homer Simpson	5	Carl Carlson	2	Waylon Smithers	3	Montogomery Burns
2	Waylon Smithers	3	Montogomery Burns	6	Canary Montogomery Burns
3	Montogomery Burns	6	Canary Montogomery Burns
4	Lenny Leonard	2	Waylon Smithers	3	Montogomery Burns	6	Canary Montogomery Burns
5	Carl Carlson	2	Waylon Smithers	3	Montogomery Burns	6	Canary Montogomery Burns
6	Canary Montogomery Burns
7	Mindy Simmons	2	Waylon Smithers	3	Montogomery Burns	6	Canary Montogomery Burns

[Greg_Deckler · Accepted solution]

@ShaneHi 

ManagerID2 = 
    VAR __ManagerID = [ManagerID]
    VAR __Result = MAXX( FILTER('Table', [UserID] = __ManagerID), [ManagerID] )
RETURN
    __Result

ManagerID2 Name = 
    VAR __ManagerID = [ManagerID]
    VAR __Result = MAXX( FILTER('Table', [UserID] = __ManagerID), [ManagerName] )
RETURN
    __Result

ManagerID3 = 
    VAR __ManagerID = [ManagerID2]
    VAR __Result = MAXX( FILTER('Table', [UserID] = __ManagerID), [ManagerID] )
RETURN
    __Result

ManagerID3 Name = 
    VAR __ManagerID = [ManagerID2]
    VAR __Result = MAXX( FILTER('Table', [UserID] = __ManagerID), [ManagerName] )
RETURN
    __Result

[Greg_Deckler · Accepted solution]

@ShaneHi 

ManagerID2 = 
    VAR __ManagerID = [ManagerID]
    VAR __Result = MAXX( FILTER('Table', [UserID] = __ManagerID), [ManagerID] )
RETURN
    __Result

ManagerID2 Name = 
    VAR __ManagerID = [ManagerID]
    VAR __Result = MAXX( FILTER('Table', [UserID] = __ManagerID), [ManagerName] )
RETURN
    __Result

ManagerID3 = 
    VAR __ManagerID = [ManagerID2]
    VAR __Result = MAXX( FILTER('Table', [UserID] = __ManagerID), [ManagerID] )
RETURN
    __Result

ManagerID3 Name = 
    VAR __ManagerID = [ManagerID2]
    VAR __Result = MAXX( FILTER('Table', [UserID] = __ManagerID), [ManagerName] )
RETURN
    __Result

[ShaneHi]

Thanks so much for this Greg - that works perfectly as I asked.

I'm going to be cheeky with a follow up ask if thats okay?

Would it be possible for me to add a further filter/query to the code?

In the table I've added a new column for "Job Role", and i've changed columns F&G to relate specifically to pulling the Operation Manager name.

Is that possible?

A	B	C	D	E	F	G
UserID	UserName	Job Role	ManagerID	ManagerName	(new column) Operation Manager ID	(new column) Operation Manager Name
1	Homer Simpson	Advisor	5	Carl Carlson	2	Waylon Smithers
2	Waylon Smithers	Operation Manager	3	Montogomery Burns
3	Montogomery Burns	Manager	6	Canary Montogomery Burns
4	Lenny Leonard	Senior Advisor	2	Waylon Smithers	2	Waylon Smithers
5	Carl Carlson	Senior Advisor	2	Waylon Smithers	2	Waylon Smithers
6	Canary Montogomery Burns	Owner
7	Mindy Simmons	Advisor	2	Waylon Smithers	2	Waylon Smithers

[v-nuoc-msft · Accepted solution]

Hi @ShaneHi 

@Greg_Deckler Thank you very much for your prompt reply. Please allow me to follow up on this post.

For your question, here is the method I provided:

(new column) Operation Manager ID = 
var _ManagerName = [ManagerName]
var _result = MAXX(FILTER('Table', [UserName] = _ManagerName), [ManagerID])
var _UserID = MAXX(FILTER('Table', [Job Role] = "Operation Manager"), [UserID])
RETURN IF( _result = _UserID || [ManagerID] = _UserID, _UserID, BLANK())

(new column) Operation Manager Name = 
var _OperationManagerID = [(new column) Operation Manager ID]
var _Result = MAXX(FILTER('Table', _OperationManagerID = [UserID]), [UserName])
RETURN _Result

Here is the result.

Regards,

Nono Chen

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