Forum Discussion
[Issue] Dynamic RLS using Org Hierarchy but over last 24 months
Hi all,
I've built a dynamic RLS for Org Hierarchy (HR Data) based on RADACAD's post
It's all looking good and working fine IF leaders are looking at current data based on current structure. However, there will be time-series chart that goes back to 12-24 months, or when leaders pick a date 18 months ago to look at headcount.
As with all organisations, there are employee movements, transfers, and restructures.
The complication comes when today, User A is a leader of Sales function and can see everyone currently in Sales; however, 2 months ago, 2 employees were transfered from Finance, and selecting a period of last 2 months will show the following table data (instead of all being in Sales, note User A should not see other function's data).
| Function | Headcount |
| Sales | 11 |
| Finance | 2 |
Ideal outcome: User A can only see data from Sales regardless of what period he/she selects (historically), in the example above, User A should see Sales = 11 headcount only
My current RLS DAX in Manage View is
PATHCONTAINS(User[Path], MaxX( Filter( User, [Email]=USERPRINCIPALNAME() ) ,User[ID] ) )
Are there any posts / solutions anyone came across that can solve this issue?
2 Replies
ncbf87 , This seems like a case of SCD
Guyinacube - https://www.youtube.com/watch?v=tKeaQpWynzg
https://www.youtube.com/watch?v=E1ZABKBpkdg
https://www.zartis.com/scd-implementation-with-temporal-tables-in-power-bi/
Thanks amitchandak ,
While you're right it's a type 2 SCD, is there an easier way to workaround the RLS where it uses the department/org hierarchy as reference?
i.e. if User A is a leader in Sales, only filter (RLS) and show data from Sales function in the last 24 months
My current RLS DAX formula in Manage Role is
PATHCONTAINS(User[Path], MaxX( Filter( User, [Email]=USERPRINCIPALNAME() ) ,User[ID] ) )
Wonder if there's any tweak to include an "IF" statement there per my logic above?
