Forum Discussion
Toggling dynamic row level security
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 [email protected] All 002 Bananas Adam … ░ 002 [email protected] All 003 Carrots Brad … ░ 003 [email protected] All 004 Dates Brad … ░ 004 [email protected] All 005 Eggplants Charlie … ░ 005 [email protected] All ║ ░ 001 [email protected] Mine ╚══════ ══════ ⇔ ════ ══ ═ 002 [email protected] Mine ░ 003 [email protected] Mine ░ 004 [email protected] Mine ░ 005 [email protected] Mine RLS:
Filter on Owner using employee org chart
░ RLS:
Or(DIM_Cost_Center_Selection[Email Address]=USERPRINCIPALNAME(), DIM_Cost_Center_Selection[Selection]="All")
- I applied bidirectional filtering to the new table, but did not apply security in both directions.
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.
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.
| ░ | |||||||
| Cost Center | Cost Center Name | Owner | … | ░ | Cost Center | Email Address | Selection |
| 001 | Apples | Adam | … | ░ | 001 | [email protected] | All |
| 002 | Bananas | Adam | … | ░ | 002 | [email protected] | All |
| 003 | Carrots | Brad | … | ░ | 003 | [email protected] | All |
| 004 | Dates | Brad | … | ░ | 004 | [email protected] | All |
| 005 | Eggplants | Charlie | … | ░ | 005 | [email protected] | All |
| ║ | ░ | 001 | [email protected] | Mine | |||
| ╚══════ | ══════ ⇔ | ════ | ══ | ═ | 002 | [email protected] | Mine |
| ░ | 003 | [email protected] | Mine | ||||
| ░ | 004 | [email protected] | Mine | ||||
| ░ | 005 | [email protected] | Mine | ||||
RLS: | Filter on Owner using employee org chart | ░ | RLS: | Or(DIM_Cost_Center_Selection[Email Address]=USERPRINCIPALNAME(), DIM_Cost_Center_Selection[Selection]="All") |
Thanks so much for this solution, this works perfectly for me!