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 

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