Search and return a column for mangers name/ID based on department

[Anonymous]

I have a data similar to the table below.

I want to create a column in this same table called [Manager ID] that would search the Employee ID column and return each employee manager's ID matching based on their department and if position is not manager.

Some positions are also blank in the real data just as we have in the table.

Employee ID	Position	Department
A	Manager	Accounting
B	Non-supervisory	Accounting
C	Manager	HR
D	Head	Accounting
E		HR
F	Non-supervisory	HR
G	Manager	Customer Service
H	Non-supervisory	Customer Service
I	Non-supervisory	Customer Service
J	Non-supervisory	HR
K	Head	Customer Service
L	Head	HR
M	Non-supervisory	HR
N		Customer service
O	Non-supervisory	Accounting

[PhilipTreacy · Accepted solution]

HI @Anonymous 

Download sample PBIX file

For a column use this

Manager ID Col = CALCULATE(MAX('DataTable'[Employee ID]), FILTER(('DataTable'), 'DataTable'[Department] = EARLIER('DataTable'[Department]) && 'DataTable'[Position] = "Manager"))

Or this if you don't want a manager reported for each Manager 

Manager ID Col 2 = 

VAR _Manager = CALCULATE(MAX('DataTable'[Employee ID]), FILTER(('DataTable'), 'DataTable'[Department] = EARLIER('DataTable'[Department]) && 'DataTable'[Position] = "Manager"))

RETURN

IF('DataTable'[Position] = "Manager", "", _Manager )

Regards

Phil

[Anonymous]

Thanks, this works but it didn't quite solve the problem I was hoping it would so I'll explain further.

My data contains a column of position level, position code, department, report to code (the position code of who you are reporting to).

I am trying to create a path to use for Row level Security and I get an error because a 'report to code' does not exist in the 'position code'. The code '0000' was assigned to "ancillary personnel".

Manager = Path('Table'[Position code],'Table'[Report to code])

error: The value '0000' in 'Table'[Report to code] must also exist in 'Table'[Position code]. Add missing data and try again

To bypass this error, I want to ascribe a 'report to code' to those individuals based on their department. The different levels of management could be "head", "manager" or "general manger" and if any exist in their department, that is who they would report to. 

First I want to create a column as such:

If 'report to code' is '0000', find head || manager || general manger in position level for same department and assign to the indiviual, else return 'report to code'

So this new column should be able to work for the RLS path.

[PhilipTreacy]

Hi @Anonymous 

Download sample PBIX file

Try this measure

Manager ID = CALCULATE(MAX('DataTable'[Employee ID]), FILTER(ALL('DataTable'), 'DataTable'[Department] = SELECTEDVALUE('DataTable'[Department]) && 'DataTable'[Position] = "Manager"))

Which gives this

If you don't want a Manager ID reported for Managers use this measure instead

Manager ID = 

VAR _Manager = CALCULATE(MAX('DataTable'[Employee ID]), FILTER(ALL('DataTable'), 'DataTable'[Department] = SELECTEDVALUE('DataTable'[Department]) && 'DataTable'[Position] = "Manager"))

RETURN

IF(SELECTEDVALUE('DataTable'[Position]) = "Manager", "" , _Manager)

Regards

Phil

[Anonymous]

Thanks but how can I make it work as a column instead of a measure