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.
I have a table that looks as follows:
ID Name Mgr Name level Parent ID
56 Sh Ja 1 43
139 Du Ja 1 43
220 Re Ja 2 43
486 Ca Ja 3 24
43 Ja Fe 1 23
55 Re Sh 1 56
486 Ca Me 1 24
24 Me .........
- Name is the employee name who is associated with manager.
Ex: sh is an employee whose direct manager is Ja. Hence level = 1. Similar is the case for Emp Name Du
Re on the other hand is an emp who reports to, say Sh (Emp in 1st row). And Sh reports to Ja. Hence Re's level to Ja =2
Parent ID is a calculated column based on lookup ffunction:
Parent ID = lookupvalue (Table[id], Emp Name, Mgr Name)
But as you can see in the above example, all parent ID's get 43 (43 = ID of Ja; Last purple highlighted row). But I need Parent ID to show the ID of the manager whose Level =1
For Ex: When Re has Mgr = Ja and Level = 2; my parent ID formula pops up Ja's ID as Parent ID. But what I need it to show is ID of Re's manager when Level =1 (In this case 56; Last line in data table -> Re reports to Sh directly and hence level =1. Sh's emp ID is 56 hence she gets parent ID = 56. )
That is, For row 3 in the dataset, even if Re reports to Ja as Level 3, the parent ID for this row should be 56 (Where 56 is ID of Sh who is the Level 1 manager for Re)
I need to modify my formula for Parent ID to reflect this.
Ideal output:
ID Name Mgr Name level Parent ID
56 Sh Ja 1 43
139 Du Ja 1 43
220 Re Ja 2 43
486 Ca Ja 3 24
43 Ja Fe 1 23
55 Re Sh 1 56
486 Ca Me 1 24
24 Me .........
Solved! Go to Solution.
hi, @Anonymous
You could use this formula to get your requirement;
Parent ID = var _minlevel=CALCULATE(MIN('Table'[Level]),FILTER('Table','Table'[Name]=EARLIER('Table'[Name]))) return var _directmgrname=CALCULATE(MAX('Table'[Mgr Name]),FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&'Table'[Level]=_minlevel)) return LOOKUPVALUE('Table'[ID],'Table'[Name],_directmgrname)
Result:
Regards,
Lin
hi, @Anonymous
You could use this formula to get your requirement;
Parent ID = var _minlevel=CALCULATE(MIN('Table'[Level]),FILTER('Table','Table'[Name]=EARLIER('Table'[Name]))) return var _directmgrname=CALCULATE(MAX('Table'[Mgr Name]),FILTER('Table','Table'[Name]=EARLIER('Table'[Name])&&'Table'[Level]=_minlevel)) return LOOKUPVALUE('Table'[ID],'Table'[Name],_directmgrname)
Result:
Regards,
Lin
Power BI data cannot represent hierarchies directly. DAX has a couple of built in functions to address this problem. You should probably try to look at them and then use them to address this problem rather than trying to roll your own. Check out this post https://docs.microsoft.com/en-us/dax/understanding-functions-for-parent-child-hierarchies-in-dax
"DAX provides five functions to help users manage data that is presented as a parent-child hierarchy in their models. With this functions a user can obtain the entire lineage of parents a row has, how many levels has the lineage to the top parent, who is the parent n-levels above the current row, who is the n-descendant from the top of the current row hierarchy and is certain parent a parent in the current row hierarchy?"
Help when you know. Ask when you don't!
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 |
---|---|
113 | |
98 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |