Toggling dynamic row level security

[Anonymous]

I'm trying to figure out how I can toggle row-level fitering or security for a certain use-case.  My customers are expecting to be able to toggle between seeing:

• All of the data that their (in)direct reports have access to (All)
• Only data they have access to, but not their direct reports (Mine)

Suppose there is a situation where an organization chart exists along the lines of:

Adam	President
	Brad	VP
		Charlie	Manager

In a seperate table, suppose cost centers are defined as:

Cost center	#Owner
001	Adam
002	Adam
003	Brad
004	Brad
005	Charlie

The user should be able to toggle between these binary options and not have to apply a filter specifying their name, employee number or anything else that isn't a boolean TRUE/FALSE.

I've able to each person to see their data and everyone's beneath them, but can't figure out this toggle situation.  Does anyone have a solution for both Adam and Brad being able to toggle between seeing only their own data vs. their entire umbrella's data?  

[parry2k]

@Anonymous there are lot of blogs/posts available to handle these or similar scenarios, here is link to one, you should pretty much able to change it to meet your needs.

[Anonymous]

@parry2k ,

Perhaps I didn't explain my problem statement as clearly as I thought.  The example you referenced is something I have already solved for, which is using RLS to let "each person see their data and everyone's beneath them."  What it doesn't address is enabling a person to easily hide their subordinate's data with the use of a binary option.

Referencing the situation I originally outlined, it was easy for me to use RLS so that:

• Adam can see data associated with cost centers {001,002,003,004,005},
• Brad can see {003,004,005}, and
• Charlie can see {005}.

What I haven't been able to find is being able to use a YES/NO or Mine/All toggle so that:

• Adam only sees {001,002},
• Brad only sees {003,004}, and
• Charlie still sees {005}.

Yes, I could have a slicer that lists the names of the employees, but if a leader has 100 people below him/her, then it is no longer convenient for the end user to perform "just show me mine." 

While I can write the measures necessary to define the logic for what to hide/show,

1. Power BI doesn't let me use the same code in a calculated column because it depends on the USERPRINCIPALNAME() function
2. We can't apply measures to report-level or page-level filters

Edit:  Another wrinkle in this situation is that a person can be assigned to multiple RLS roles, so bidirectional filtering is another function that seems to be out-of-the-question....

Regards,

Rob

[parry2k]

@Anonymous aha, so let me see if I understood if correctly

- you already solved where a user can see his own department and employee under him, correct?

Problem we need to solve:

there is a selection (may be a slicer) to toggle between 

- see his own department only

- see his own and his team department

so based on selection you want to show the data, correct?

If this is correct understanding, I would request you to send me pbix file (remove any sensitive information) and it can be achieved. We have to do the logic in RLS formula based on selection, not thru filter etc.

[Anonymous · Accepted solution]

I was able to work out a solution by adding another dimensional table and applying RLS against that.

• I applied bidirectional filtering to the new table, but did not apply security in both directions.
  • This way, with RLS applied to DIM_Cost_Center, I start with only the cost centers that each employee is permitted to see.
  • This table then filters which cost centers are accessible from DIM_Cost_Center_Selection[Selection]="All".
• The second RLS filter limits which records are visibile in DIM_Cost_Center_Selection[Selection]="Mine"

Now I'm able to throw slicers on the canvas where the end-user specifies DIM_Cost_Center_Selection[Selection] and all visuals (including additional slicers) update accordingly.

	DIM_Cost_Center			░		DIM_Cost_Center_Selection
Cost Center	Cost Center Name	Owner	…	░	Cost Center	Email Address	Selection
001	Apples	Adam	…	░	001	Adam@Contoso.com	All
002	Bananas	Adam	…	░	002	Adam@Contoso.com	All
003	Carrots	Brad	…	░	003	Brad@Contoso.com	All
004	Dates	Brad	…	░	004	Brad@Contoso.com	All
005	Eggplants	Charlie	…	░	005	Charlie@Contoso.com	All
║				░	001	Adam@Contoso.com	Mine
╚══════	══════  ⇔	════	══	═	002	Adam@Contoso.com	Mine
				░	003	Brad@Contoso.com	Mine
				░	004	Brad@Contoso.com	Mine
				░	005	Charlie@Contoso.com	Mine
RLS:	Filter on Owner using employee org chart			░	RLS:	Or(DIM_Cost_Center_Selection[Email Address]=USERPRINCIPALNAME(), DIM_Cost_Center_Selection[Selection]="All")

[Anonymous]

Thanks so much for this solution, this works perfectly for me!