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

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!