Forum Discussion
Dynamic Row Level Security Equation not working with Date Filter
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 | [email protected] | 143466 | 143466 | |
| 2 | Matthew | 1/1/2019 | [email protected] | 243466 | 243466 | |
| 3 | David | 1/1/2019 | [email protected] | 1 | 343466 | 143466 |
| 4 | Mark | 1/1/2019 | [email protected] | 2 | 443466 | 243466 |
| 5 | Amy | 1/1/2019 | [email protected] | 1 | 543466 | 143466 |
| 6 | Bill | 1/1/2019 | [email protected] | 4 | 643466 | 443466 |
| 7 | Justin | 1/1/2019 | [email protected] | 2 | 743466 | 243466 |
| 8 | Lindsay | 1/1/2019 | [email protected] | 5 | 843466 | 543466 |
| 1 | Mary | 2/1/2019 | [email protected] | 143497 | 143497 | |
| 2 | Matthew | 2/1/2019 | [email protected] | 243497 | 243497 | |
| 3 | David | 2/1/2019 | [email protected] | 1 | 343497 | 143497 |
| 4 | Mark | 2/1/2019 | [email protected] | 2 | 443497 | 243497 |
| 5 | Amy | 2/1/2019 | [email protected] | 1 | 543497 | 143497 |
| 6 | Bill | 2/1/2019 | [email protected] | 4 | 643497 | 443497 |
| 7 | Justin | 2/1/2019 | [email protected] | 3 | 743497 | 343497 |
| 8 | Lindsay | 2/1/2019 | [email protected] | 5 | 843497 | 543497 |
| 1 | Mary | 3/1/2019 | [email protected] | 143525 | 143525 | |
| 2 | Matthew | 3/1/2019 | [email protected] | 243525 | 243525 | |
| 3 | David | 3/1/2019 | [email protected] | 1 | 343525 | 143525 |
| 4 | Mark | 3/1/2019 | [email protected] | 2 | 443525 | 243525 |
| 5 | Amy | 3/1/2019 | [email protected] | 1 | 543525 | 143525 |
| 6 | Bill | 3/1/2019 | [email protected] | 4 | 643525 | 443525 |
| 7 | Justin | 3/1/2019 | [email protected] | 3 | 743525 | 343525 |
| 8 | Lindsay | 3/1/2019 | [email protected] | 7 | 843525 | 743525 |
The equation I am using:
PATHCONTAINS(Sheet1[Path],
MaxX(
Filter(
Sheet1,
[Email]=USERPRINCIPALNAME()
)
,Sheet1[UserID]
)
)
1 Reply
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?
