cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cnweke Member
Member

Dynamic row level security

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:security.pngIt 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?

5 REPLIES 5
Community Support Team
Community Support Team

Re: Dynamic row level security

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 Member
Member

Re: Dynamic row level security

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.

Community Support Team
Community Support Team

Re: Dynamic row level security

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

Community Support Team
Community Support Team

Re: Dynamic row level security

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 Member
Member

Re: Dynamic row level security

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 321 members 3,407 guests
Please welcome our newest community members: