topic Re: Power BI - Lookup within existing table to add new columns for missing manager information in DAX Commands and Tips

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

ShaneHi — Tue, 02 Apr 2024 09:21:14 GMT

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

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

Greg_Deckler — Tue, 02 Apr 2024 10:33:33 GMT

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

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

ShaneHi — Tue, 02 Apr 2024 15:11:01 GMT

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

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

Anonymous — Wed, 10 Apr 2024 06:49:23 GMT

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.