Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 | Path |
112 | 112 | |
14 | 112 | 112|14 |
3 | 14 | 112|14|3 |
13 | 3 | 112|14|3|13 |
162 | 3 | 112|14|3|162 |
117 | 162 | 112|14|3|162|117 |
221 | 162 | 112|14|3|162|221 |
81 | 13 | 112|14|3|13|81 |
10 | 81 | 112|14|3|13|81|10 |
Table 2 - shows at what level a manger is and where all the directs should roll up to. This can and is very dynamic as to where it can fall in the hierarchy.
Table 2 -Manager ID |
81 |
162 |
Table 3 - is a third data set that comes in with data including employee id. Employee ID can be at any level.
Table 3 - Data |
221 |
81 |
10 |
The goal is to add a column to table 3 that shows the manager id based on the data in tables 1 and 2.
Essentially, for employee 221 we'd see 162, for 81, we would see 81, and for 10, we'd see 81.
Table 3 - Enhanced with Manager ID level mapped at | |
221 | 162 |
81 | 81 |
10 | 81 |
Any help would be greatly appreciated. Thanks for your time!
Solved! Go to Solution.
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
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
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 |
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
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?
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
105 | |
77 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |