Dynamic row level security

[cnweke]

Hi guys, 

When designing my model i believed my use case would be very simple, quite the opposite.  The following picture is a representation of my model:It has 1 fact table surrounded by 13 dimensions. The dimension in the bottom right is connected to my 'Account' table and applies dynamic row level security based on the user that's logged in. Now, all that works. My concern is the following: 

EVERY dimension has account specific information only the account manager/client can see which is why I have bidirectional filters on every tabel connected to my fact. They also all have a security key.

This dynamic RLS 'works' if I use a forced select slicer on account. If for some reason this option is removed, the account specific information of every dimension is out.

The way I wanted to solve it is by connecting all my tables you my security table but then I'm hit whe the ambiguous paths error. 

A workaround would be to add one security table for each dimension.

Another solution could be to add their credentials to each table and then use a username = username() dax formula on every table.

What do you guys reccomend? Are there other ways of doing this?

[v-xicai]

Hi @cnweke ,

Please see the link Power BI Desktop Dynamic security cheat sheet, which described the detailed steps. Maybe it doesn't work, there are some tips to let it work and test it efficiently.

You can also refer to the similar case: https://community.powerbi.com/t5/Desktop/Dynamic-RLS-role-creation-based-on-Login-name/td-p/397734.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cnweke]

Hey,

Thanks for the the tips but my use case is slightly different and I've read most of those.

They're only filtering from let's say a RLS table to a region dimension to a fact.

All my 10 + dimensions have to be filtered too and I'm looking for an efficient way to 'propagate' my filter from my fact to my other dimensions. 

Currently I can do this through using a custom visual that forces the user to select a slicer element, then my RLS is properly propagated.

I'm looking for alternatives to this.

[v-xicai]

Hi  @cnweke ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[cnweke]

Hey, 

Thanks for trying but that didn't work for me. My use case is rather annoying as I don't only need to filter my fact table, I need to filter EVERY dimension because they all have rather sensitive information. 

As of today I have two bad solutions which are adding a bridge + security table onto each dimension that needs to be filtered in addition to my fact.

Create a view that combines my fact tables with my security table.

[v-xicai]

Hi @cnweke ,

For all the relationships, try to change the Cross filter direction from Single to Both, which will take them treated as a single table, and will take the 'propagate' effective.

Best Regards,

Amy