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

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
Icon for Community Champion rankCommunity 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