How do you include all data if viewing as a User who doesn't have any restrictions? [RLS]

[e_don]

Hi all,

I'm trying to incorporate some row level security into my reports.

I'm doing this by adding some DAX filtering into my dimension tables.

The first table I have is a restrictions table, which gathers all restrictions set by the organization. If the table is empty, the User has full access to the data. If there exists a row in the table, the User can only see the data in that row. 

Restriction table:

Restriction Id	User Id	Team Id
34	100	1
35	101	2

Example:

Staff List
User Id	Name	Team Id
100	John Smith	1
101	Jane Doe	2
102	Sarah Stephens	3
103	Daniel Smith	1

User Id 100 can only see data for Team Id = 1, meaning when John Smith views the Staff List, he will only be able to see data for John Smith and Daniel Smith, also in Team Id=1.

When Jane Doe views the data, she will only be able to see data for herself as she has a restriction to only view Team Id =2.
(REQUEST) When Sarah Stephens views the data, she will be able to see everything as no restriction exists against her in the Restrictions table.

The restriction table gets filtered when the user views the report:
> IF('Restriction'[User Id]=VALUE(USERNAME()),TRUE(),FALSE)

(Intentionally using Username over UsernamePrincipal here, then converting to value as its an integer).

The plan is to then evaluate my Staff list table in the dax filtering, something like 

- If Restrictions table is empty, show everything, else apply the restrictions.

The problem I seem to be having is I am struggling to find a way to evaluate the Restrictions table for it to be empty. Most functions appear to not be compatible with RLS. 

I've played around with using MIN/MAX functions but they didn't work out for me.

Thanks in advance.

[e_don · Accepted solution]

Hi all,

I decided to take a step back and combine the idea from the manager level solution @v-xiaoyan-msft provided. 

In my users table in SQL, I created a column that would search the restrictions table:

,CASE
WHEN EXISTS (
SELECT Restrictions.[User Id]
FROM Restrictions R
WHERE R.[User Id] = Staff List.[User Id]
) THEN 0
ELSE 1
END [UserCanSeeEverything]

Then my DAX expression will show everything if the user can see everything, or only shows the allowed team if there is a restriction.

 OR(
        1=LOOKUPVALUE('Staff List'[UserCanSeeEverything], 'Staff List'[User Id],VALUE(USERNAME()))
,
        'Staff List'[Team Id]=LOOKUPVALUE(
            'Restrictions' [Team Id],
            'Restrictions'[User Id],
            VALUE(USERNAME())
        )

) 

[v-xiaoyan-msft]

Hi @e_don ,

RLS may not be applied well by your current method.
We recommend that you switch to another workaround.

This can be done with Username() or UserPrincipalName() functions in DAX.

Here is an article that explains the steps in detail:Dynamic Row Level Security with Manager Level Access in Power BI 

If I have not understood your needs correctly, please do not hesitate to inform me.

Hope it helps,

Community Support Team _ Caitlyn Yan

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Use COALESCE to catch the BLANK() scenario and decide what to do in such a case.  One of the possible approaches is to have an "All" RLS rule that you could stuff these users in.