Using hierarchy to enhance another data set

[Jharts17]

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!

[az38]

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

[Jharts17]

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

[az38 · Accepted solution]

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

[Jharts17]

Hey @az38 ,

I did encounter an issue with this, wondering if you're able to assist.

1|20488|4462||4586|24739|4587 ----mapped to manager ID 24739

1|20488|3|5|6|247-----mapped to manager ID 247

Both rows return 247.

It appears the "search" function is registering 247. What's the easiest way to turn this into an exact search?

[az38]

@Jharts17 

try more complex calculated 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)=(len(RELATED('Table'[Path]))-len('Table 2'[Manager ID]));
        SEARCH(CONCATENATE('Table 2'[Manager ID];"|");RELATED('Table'[Path]);1;-1)=1)
)))

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[Jharts17]

At first it wasn't working. For anyone that looks at this, make sure the relationship between Table 1 and 3 is not uni-directional.

AZ, you are a beaut! I spot checked at least 20 or so and this formula is returning exactly what I needed. Appreciate your help!