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 data set that looks like the following:
ID | Name | Manager |
1 | Adam | John |
2 | John |
I want there to be another column called 'Manager ID' that obtains the ID of each person's manager from the ID column. Desired result:
ID | Name | Manager | ManagerID |
1 | Adam | John | 2 |
2 | John |
I'm not sure how to achieve this. Thanks for your help.
Solved! Go to Solution.
Hi @Anonymous ,
Please refer to my .pbix file.
I'm not sure if you want calculated column or measure, so both are created.
In addition, [__Maganer] is a necessary condition for calculation. Although you don't need it, the final result can not be obtained without it.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous
You may use Power Query for such scenarios,
Refer to the following article for more details:
https://www.vivran.in/post/power-query-quickbytes-1-self-join
Hope this helps.
Cheers!
Vivek
If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter
Hi @Anonymous ,
Please do like this.
Create two calculated columns.
(You can also combine these two formulas.)
__Manager =
IF(
[Manager] = BLANK(),
[Name], [Manager]
)
Column =
VAR x =
CALCULATE(
MAX(Sheet4[ID]),
FILTER(
Sheet4,
Sheet4[__Manager] = EARLIER(Sheet4[__Manager]) && Sheet4[Manager] = BLANK()
)
)
RETURN
IF(
[Manager] = BLANK(),
BLANK(), x
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for getting back to me. I don't need that additonal '___ Manager' column and due to that, the formula doesn't work. Any gaps where someone is the top of the hierarchy will be like this:
ID | Name | Manager | Manager ID |
1 | Adam | John | 2 |
2 | John | John | 2 |
But I can't seem to get your second calculated column to work. Any ideas?
Thanks
Hi @Anonymous ,
Please refer to my .pbix file.
I'm not sure if you want calculated column or measure, so both are created.
In addition, [__Maganer] is a necessary condition for calculation. Although you don't need it, the final result can not be obtained without it.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Create a calculated column like this.
Column =
VAR x =
CALCULATE(
MAX(Sheet3[ Name Manager]),
FILTER(
Sheet3,
Sheet3[ID] = EARLIER(Sheet3[ID]) + 1 && MOD( EARLIER(Sheet3[ID]), 2 ) <> 0
)
)
VAR y =
IF(
x = BLANK(),
" ", x
)
RETURN
IF(
CONTAINSSTRING(Sheet3[ Name Manager],y),
Sheet3[ID]+1, BLANK()
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-lionel-msft,
I'm not sure if your solution is applicable as I believe you have misunderstood my table (which isn't clear to be fair!) as Name and Manager are separate columns:
ID | Name | Manager | Manager ID |
1 | Adam | John | 2 |
2 | John |
What I have entered in red in the desired result I need to achieve.
Thanks
@Anonymous - You can use LOOKUPVALUE for that. Or follow the Lookup Min/Max pattern. https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
Also, if you create a concatenated column with a | character between then you could just use PATH
https://docs.microsoft.com/en-us/dax/path-function-dax
I've tried LOOKUPVALUE but that doesn't seem to achieve what I required - unless I am using it incorrectly?
In terms of the concentrated column, would that be ID|Display Name to use PATH to pull the ID of each person's manager?
Thanks
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 |
---|---|
12 | |
2 | |
2 | |
1 | |
1 |