Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
ShaneHi
Frequent Visitor

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

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?

 

ABCDEFGH
UserIDUserNameManagerIDManagerName(new column) Manager2ID(new column) Manager2Name(new column) Manager2ID(new column) Manager2Name
1Homer Simpson5Carl Carlson2Waylon Smithers3Montogomery Burns
2Waylon Smithers3Montogomery Burns6Canary Montogomery Burns  
3Montogomery Burns6Canary Montogomery Burns    
4Lenny Leonard2Waylon Smithers3Montogomery Burns6Canary Montogomery Burns
5Carl Carlson2Waylon Smithers3Montogomery Burns6Canary Montogomery Burns
6Canary Montogomery Burns      
7Mindy Simmons2Waylon Smithers3Montogomery Burns6Canary Montogomery Burns
2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

@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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

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.

 

vnuocmsft_0-1712731695030.png

 

Regards,

Nono Chen

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

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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?

 

AB CDEFG
UserIDUserNameJob RoleManagerIDManagerName(new column) Operation Manager ID(new column) Operation Manager Name
1Homer SimpsonAdvisor5Carl Carlson2Waylon Smithers
2Waylon SmithersOperation Manager3Montogomery Burns  
3Montogomery BurnsManager6Canary Montogomery Burns  
4Lenny LeonardSenior Advisor2Waylon Smithers2Waylon Smithers
5Carl CarlsonSenior Advisor2Waylon Smithers2Waylon Smithers
6Canary Montogomery BurnsOwner    
7Mindy SimmonsAdvisor2Waylon Smithers2Waylon Smithers

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.

 

vnuocmsft_0-1712731695030.png

 

Regards,

Nono Chen

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.