[Issue] Dynamic RLS using Org Hierarchy but over last 24 months

[ncbf87]

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? 

[ncbf87]

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?

[amitchandak]

@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/