Forum Discussion

Jharts17's avatar
Jharts17
Regular Visitor
6 years ago
Solved

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...
Need Help
  • az38's avatar
    az38
    6 years ago

    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's avatar
Jharts17
Regular Visitor
6 years ago

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 
221162
8113
1013
  
az38's avatar
az38
Community Champion
6 years ago

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's avatar
    Jharts17
    Regular Visitor
    6 years ago

    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!

  • Jharts17's avatar
    Jharts17
    Regular Visitor
    6 years ago

    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's avatar
      az38
      Community Champion
      6 years ago

      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