Forum Discussion
Using hierarchy to enhance another data set
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
Community ChampionHi 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 - az38
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
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!
