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 created a hierarchy row level security role in power bi desktop which works as long as there is not date filter on the report. I am trying to create a human resources report that will show the number of resources we have in the company and show their managers who are their direct reports. The HR department wants to see this on a monthly basis and wants to send it out to managers but only allow them to see their hierarchy in the company. The equation works, but not when I have the date filter. I have some test data and the equation below. Any help with this would be greatly appreciated.
ID | Name | Date | ManagerID | UserID | MgrID | |
1 | Mary | 1/1/2019 | Mary@test.com | 143466 | 143466 | |
2 | Matthew | 1/1/2019 | Matthew@test.com | 243466 | 243466 | |
3 | David | 1/1/2019 | David@test.com | 1 | 343466 | 143466 |
4 | Mark | 1/1/2019 | Mark@test.com | 2 | 443466 | 243466 |
5 | Amy | 1/1/2019 | Amy@test.com | 1 | 543466 | 143466 |
6 | Bill | 1/1/2019 | Bill@test.com | 4 | 643466 | 443466 |
7 | Justin | 1/1/2019 | Justin@test.com | 2 | 743466 | 243466 |
8 | Lindsay | 1/1/2019 | Lindsay@test.com | 5 | 843466 | 543466 |
1 | Mary | 2/1/2019 | Mary@test.com | 143497 | 143497 | |
2 | Matthew | 2/1/2019 | Matthew@test.com | 243497 | 243497 | |
3 | David | 2/1/2019 | David@test.com | 1 | 343497 | 143497 |
4 | Mark | 2/1/2019 | Mark@test.com | 2 | 443497 | 243497 |
5 | Amy | 2/1/2019 | Amy@test.com | 1 | 543497 | 143497 |
6 | Bill | 2/1/2019 | Bill@test.com | 4 | 643497 | 443497 |
7 | Justin | 2/1/2019 | Justin@test.com | 3 | 743497 | 343497 |
8 | Lindsay | 2/1/2019 | Lindsay@test.com | 5 | 843497 | 543497 |
1 | Mary | 3/1/2019 | Mary@test.com | 143525 | 143525 | |
2 | Matthew | 3/1/2019 | Matthew@test.com | 243525 | 243525 | |
3 | David | 3/1/2019 | David@test.com | 1 | 343525 | 143525 |
4 | Mark | 3/1/2019 | Mark@test.com | 2 | 443525 | 243525 |
5 | Amy | 3/1/2019 | Amy@test.com | 1 | 543525 | 143525 |
6 | Bill | 3/1/2019 | Bill@test.com | 4 | 643525 | 443525 |
7 | Justin | 3/1/2019 | Justin@test.com | 3 | 743525 | 343525 |
8 | Lindsay | 3/1/2019 | Lindsay@test.com | 7 | 843525 | 743525 |
The equation I am using:
PATHCONTAINS(Sheet1[Path],
MaxX(
Filter(
Sheet1,
[Email]=USERPRINCIPALNAME()
)
,Sheet1[UserID]
)
)
I'm assuming [Path] is a measure or calculated column you've created to give you a path string of the hierarchy? Could you share that expression with us?
As it stands, I'm curious: does it work when the date filter is set to the most recent date?
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 |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |