Filtering column by dynamic role

[Anonymous]

Hi all,

As a newbeginner this might be a very silly question. Still, after hours of googleing I haven't managed to find the solution on my own.

I'm using a report that can be filtered by a selected "CompanyID" using the row level security technique. I have tried to take the report to the next level by comparing the selected company to the all the companies that share the same "Branch" with the selected company. The goal is to visualize the total of "PersonID" in each "Group" value.

So my question is: How to tell Power BI to select every company in one Branch when the CompanyID is not static? 

I simplified the model to clarify the situation. Hopefully this makes any sense for you.

Thank you in advance!

Kind regards,

Julia

[lbendlin · Accepted solution]

That's actually a conflict.  If you are limiting visibility to a certain company by using RLS then that will prevent that user from seeing the data from the other companies.

Reza Rad describes that scenario in great detail, but the gist is that you would need to create a second shadow set of tables that is hidden from view, and not governed by RLS.  That way you can compare the current selection to the complete data set.

HUGE WARNING:  Security by Obscurity does not work.  Any sufficiently knowledgeable user can crack this setup in seconds and get access to data they should not see based on the RLS.

[Anonymous]

That's what I thought. Adding tables made it work as wanted. Thank you for your guidance!

Julia