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 an organizational chart that looks somewhat like this:
The data is in a table like below:
EMPLOYEE,MANAGER
Nicole,
Jim,Nicole
Dana,Nicole
William,Jim
Maria,Jim
Kate,Jim
Jane,Dana
John,Dana
Stewart,Kate
Scott,John
Osha,John
Then I have a second table that tracks some data for each employee. Suppose it's some task completion data.
EMPLOYEE,TASKS,COMPLETED
Nicole,12,8
Jim,10,5
Dana,8,8
William,13,11
Maria,15,13
Kate,4,4
Jane,16,12
John,3,3
Stewart,6,5
Scott,23,21
Osha,9,3
Based on this data I calculate a completion %.
So far, all good. But I'd like to be able to have a slicer and filter by manager, and when it does, I'd like to show data for all employees that directly report to that manager as well as those who report to managers under him/her. For instance, from above data if I select Dana, I'd like to show data for all four Jane, John, Scott, and Osha. But as it is if I select Dana it only shows data for employees directly report to her.
How do I set the relationships to display what I want. I don't have control over how the data source is organized, so all I do will have to be from within PowerBI.
It's a nice question, and I am looking for a similar soln to my problem.
A potential solution could be to add a new column using the LOOKUPVALUE function, see an example below:
Also, the below article provides more information on the lookupvalue function if you want to make any changes to the logic:
https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax
This works, but only for the very specific example I asked. For instance it only basically shows for second level managers reporting to Nicole, but if I select Nicole it'll only show data for Jim and Dana.
Organization structure can often change, and there can be managers added or removed chaning the 'level' etc. I wonder if there's a more general solution.
Need solution facing exact same issue.
Did you get a solution to this one ?
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |