SCD Type 2 in Row Level Security

[stevenvd]

Hi,

I need to apply dynamic RLS. I have implemented this before but now the user table is of SCD type 2. 

So I have a table 'Query' that contains the AD users with the company they have access to + start date and end date. A user can have access to a certain company until a certain date and then the user has no access anymore but he can then again get access. The user can only see data in the fact table for dates in which the user was allowed to see data. So in the example below the user can see data in the fact from Jan 1 2000 till March 15 2018, between March 15 2018 and August 18 2018 the user cannot see data from the fact, and from August 18 2018 onwards he can again see always data. 

I have tried many things in the DAX filter in RLS but cannot figure it out. The filter should be something like USERNAME = USERPRINCIPALNAME() to filter the companies and then the fact table should have a filter "posting date" > START_DATE and "posting date" <= END_DATE. 

In this example there are 2 rows with the same user and the same company. If there was only 1 row it would have been easy but now I am lost.  Any direction or idea you may have, is more than welcome 🙂

I have thought about working with foreign keys with SQL but the real scenario is more complex and seems impossible to do with SQL.

[parry2k]

@stevenvd  I think it is doable but would like to test at my end and send you the solution (pbix) file. if you can put sample data of few users trnsaction data in excel that will be great. Don't have much time to create daataset at my end to put the solution together.