Dynamic RLS Toggle?

[HI_IM_THIBBY]

Hello,

I craeted a switch in my RLS DAX like the below:

[Person] =
--Off = security becomes inactive
--On = security is active

IF(
    SELECTEDVALUE('Security Switch'[Switch]) = "Off", BLANK(),
    LOOKUPVALUE(
        'Security Table'[Person Name],
        'Security Table'[Person Email Address], USERPRINCIPALNAME(),
        'Security Table'[Person Position], "Level 2"))

I'm basically wanting the security to filter the table if a toggle I created is set to "Off" on the page. I'm currently using BLANK() but it doesn't work. I've used a SELECTEDVALUE([Person] and that was also unsuccessful. Is this possible to achieve?

Thanks,

Thibby

[HI_IM_THIBBY]

I'm sorry, I think I phrased my question incorrectly. Currently I have a Security Toggle table and a Security Table but I have a 3rd table that is actually using the DAX security filters. By default security is in place, but when I use this security toggle, I want there to be no security so the user can see everything on the visual I choose.

I've tried using BLANK(), [the column I'm filtering], NULL but none of these seem to work.

So basically seeing only what a user's security allows when "On" or blank and seeing everything when toggled "Off".

[Column in 3rd table to be filtered] =
--Off = security becomes inactive
--On = security is active

IF(
SELECTEDVALUE('Security Switch'[Switch]) = "Off", BLANK(),
LOOKUPVALUE(
'Security Table'[Person Name],
'Security Table'[Person Email Address], USERPRINCIPALNAME(),
'Security Table'[Person Position], "Level 2"))

I've also experimented with this but this is also a dud.

IF(
SELECTEDVALUE('Security Toggle'[Toggle]) = "Off", BLANK(),
[Column in 3rd table I want to filter] = LOOKUPVALUE(
'Security Table'[Person Name],
'Security Table'[Person Email Address], USERPRINCIPALNAME(),
'Security Table'[Person Position], "Level 2")
)

[v-juanli-msft]

Hi @HI_IM_THIBBY

based on my knowledge, you may be in the incorrect direction.

you want to create a new column in a third table to reflect the changes when you select different items from the slicer.

However, calculated columns doesn't change with slicer. 

In you scenario, you need to consider measure.

I work with a workaround.

copy the table "Security Table" to get a new table called "copy_securitytable",

create a measure inside this table

Measure = SELECTEDVALUE('Security Switch'[Switch])

Best regards

Maggie

[HI_IM_THIBBY]

Apologies for the late reply, but I'm still confused about this. This is going to be security throughout my entire dashboard, so putting a measure into each visual is not something I want to do. In my roles DAX code, I want to be a conditional where it'll set security to filter table 1 if the toggle is "A" and to filter table 2 when toggle is set to "B" if that makes sense. What you provided is helpful but not solving what I'm facing. 

I created a measure using this code. Filter Table is the name of the table with my 2 selections, filter type ("filter table 1" or "filter table 2").

Filter Flag =

SELECTEDVALUE('Filter Table'[Filter Type])

I am using this cade in my set roles DAX window:

IF(SELECTEDVALUE('Filter Table'[Filter Type]) = "filter table 1",
[employee_name_5] = LOOKUPVALUE(
'Security'[Name],
'Security'[Email],
USERPRINCIPALNAME(),
'AR Security'[Position],
"Level 2")
)

IF(SELECTEDVALUE('Filter Table'[Filter Type]) = "filter table 2",
[employee_name_2] = LOOKUPVALUE(
'Security'[Name],
'Security'[Email],
USERPRINCIPALNAME(),
'AR Security'[Position],
"Level 2")
)

After using the above, I just get blank slicers and blank visuals....any help?

[v-juanli-msft]

Hi @HI_IM_THIBBY

I make a test as below:

Security Table:

name	address	position
asd	account1	Level 1
daad	account2	Level 2
dgdf	account3	Level 3
asd	account1	Level 0
daad	account2	Level 1
dgdf	account3	Level 2

don't connect "Security Switch" table with "Security Table" in power bi desktop.

1.create a role in "Security Table", write the code in:

[address]=USERPRINCIPALNAME()
&&
[position]="Level 2"

2.create a measure in "Security Table", 

Measure = SELECTEDVALUE('Security Switch'[Switch])

3.don't add this measure in the table visual, but add this measure in the "Visual level filter" of the table visual

4.save and publish to power bi service to a app workplace,

where i'm owner and others are members, also members can only view content inside this app workplace. 

please see more details here: https://docs.microsoft.com/en-us/power-bi/service-create-workspaces#create-an-app-workspace-based-on...

5.set RLS in power bi service, more details please refer to: https://docs.microsoft.com/en-us/power-bi/service-admin-rls#validating-the-role-within-the-power-bi-...

6.use account 2 log in power bi service to view the report from this app workplace:

Best Regards
Maggie

“
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
“