Dynamic RLS by using a UserSecurity table

Hi NielsBakker 

I politely disagree with RemyO.

Yes it is complicated but in the real world security is complicated,

because you have users who need access to all X and Y but only some of Z

which would be very time consumming to set up and maintain in a relationship table

but very easy to confugure in your simple example table. 

If you know a little DAX then it is possible and very easywith just a few commands

Try this ....

Use -1 or * or 0 or anything except  "null" in your securitry table

Out your secret data in Fact table

Build a 1:M relationship from your Security table to your Fact table using the SUBSRIPTIONID

Add a security role with the following rules

Test using the "View as" function

  I tested it and it seems to works exactly as you requested.

Please click the thumbs up because I have tried to help,

and if it works then click the [accept solution] button.

Many thanks

Thank you NielsBakker 

Please quote @speedramps in you next question and I can show you some more advanced row level security like this ...

So Jane has access to all store in France

but just stotes 1 and 4 in UK

and all store in USA except store 1 and 3.

We can also make these rules date effective, to give staff temporay access to stores for cover during holidays and sickness.

These real life scenarios happen and need to be easy to administer via a config table.