Forum Discussion
Using hierarchy to enhance another data set
Hi all,
Looking for some assistance.
I have 3 data sets.
Table 1 - contains the unique employee id's and their parent nodes / hierarchy paths
| Table 1 | ||
| EmployeeKey | ParentEmployeeKey | Path |
| 112 | 112 | |
| 14 | 112 | 112|14 |
| 3 | 14 | 112|14|3 |
| 13 | 3 | 112|14|3|13 |
| 162 | 3 | 112|14|3|162 |
| 117 | 162 | 112|14|3|162|117 |
| 221 | 162 | 112|14|3|162|221 |
| 81 | 13 | 112|14|3|13|81 |
| 10 | 81 | 112|14|3|13|81|10 |
Table 2 - shows at what level a manger is and where all the directs should roll up to. This can and is very dynamic as to where it can fall in the hierarchy.
| Table 2 -Manager ID |
| 81 |
| 162 |
Table 3 - is a third data set that comes in with data including employee id. Employee ID can be at any level.
| Table 3 - Data |
| 221 |
| 81 |
| 10 |
The goal is to add a column to table 3 that shows the manager id based on the data in tables 1 and 2.
Essentially, for employee 221 we'd see 162, for 81, we would see 81, and for 10, we'd see 81.
| Table 3 - Enhanced with Manager ID level mapped at | |
| 221 | 162 |
| 81 | 81 |
| 10 | 81 |
Any help would be greatly appreciated. Thanks for your time!
hi Jharts17
try this column
Column = calculate(min('Table 2'[Manager ID]); filter('Table 2'; OR( SEARCH(CONCATENATE("|";CONCATENATE('Table 2'[Manager ID];"|"));RELATED('Table'[Path]);1;-1)>0; OR( SEARCH(CONCATENATE("|";'Table 2'[Manager ID]);RELATED('Table'[Path]);1;-1)>0; SEARCH(CONCATENATE('Table 2'[Manager ID];"|");RELATED('Table'[Path]);1;-1)>0) )))it finds parental in any way - if its in the start of Path, in the middle or in the finish.
and dont forget to create relationships
do not hesitate to give a kudo to useful posts and mark solutions as solution
6 Replies
Hi Jharts17
first, create relationships 'Table 3'[Data] - 'Table 1'[EmployeeKey]
then create calculated column in the table3:
Manager = if(LOOKUPVALUE('Table 2'[Manager ID];'Table 2'[Manager ID];'Table 3'[Data])='Table 3'[Data];'Table 3'[Data];related('Table 1'[ParentEmployeeKey]))do not hesitate to give a kudo to useful posts and mark solutions as solution
Thanks for taking the time to respond AZ! I didn't give the greatest of examples and that's my apologies. But the first part of the lookup function is correct, it's the second part where I get stuck. The related part is incorrect because the parent node of any given employee isn't necessarily going to be the manager id defined in table 2. The manager id defined in table 2 is also not going to be at the same leaf level in the hierarchy chain.
So in my example, it might be better if we change 81 to 13 for table 2 (ignore the fact that it is at the same leaf as 162)
Table 2 -
Table 2 -Manager ID 13 162 Table 3 -
Table 3 - Data 221 81 10 I am looking for what would return 13 and 162. The tough one being for data point 10, which has a parent of 81, but according to the mapping in table 2, needs to return 13
Table 3 - Enhanced with Manager ID level mapped at 221 162 81 13 10 13 hi Jharts17
try this column
Column = calculate(min('Table 2'[Manager ID]); filter('Table 2'; OR( SEARCH(CONCATENATE("|";CONCATENATE('Table 2'[Manager ID];"|"));RELATED('Table'[Path]);1;-1)>0; OR( SEARCH(CONCATENATE("|";'Table 2'[Manager ID]);RELATED('Table'[Path]);1;-1)>0; SEARCH(CONCATENATE('Table 2'[Manager ID];"|");RELATED('Table'[Path]);1;-1)>0) )))it finds parental in any way - if its in the start of Path, in the middle or in the finish.
and dont forget to create relationships
do not hesitate to give a kudo to useful posts and mark solutions as solution
