Row Level Security with Multiple Dimension Tables

[AnthonyJ]

Hi,

I created a proof-of-concept model with dynamic RLS attached to a fact table. There were two dimension tables coming off the fact table and the idea was that RLS would filter the Fact which, by the reduced keys, would filter the two dimension tables.

My first iteration I got this to work although I had to change the relationships to "Many-to_Many" and direction "Both". View the Role I found the dimension tables showed the correct information. However, when I tried to duplicate this, it didn't work. I tried applying the security filters but this only worked on one table associated with the fact. Finally I actually managed to get both dimension tables ticked for "Apply Security Filters" however I couldn't replicate this either.

Questions:

Are you only allowed to have one table inherit security? (Hence the error message when trying to select the second one)

The two times I managed to get this to work. Is this due to a bug? (As I haven't been able to reproduce it)

If you can only inherity security to one table, are there any plans to expand this in the future so security can flow through the rest of the model?

Thanks

[lbendlin · Accepted solution]

"the Fact which, by the reduced keys, would filter the two dimension tables."

The desired layout of the Power BI data model is a star or snowflake schema, with the dimension tables sitting on the outside and controlling the fact table(s) (not  the other way round!).  The desired RLS application is to the outermost dimension tables so that the filters can propagate down all the way to the fact tables.  So pretty much the opposite of your setup.