RLS where one of two tables is filtered depending on another table

[naninamu]

HI,

I have a situation where I have a table User. 

If AccessAllPortfolioBoards = TRUE, then Table A should have RLS applied to it based on the ID of the user.

If AccessAllPortfolioBoards = FALSE, then Table B should have RLS applied to it based on the ID of the user.

How do I do this? I am stuck as to how to set this up. Do I write some sort of conditional DAX against the tables in different roles?

Thanks in advance.

[OwenAuger · Accepted solution]

Hi again @naninamu 

Thanks for the clarification, and I'll answer the questions from both your replies.

1. Since the User table has both "User email" and "ID" columns, we can tweak the code to retrieve both the True/False flag and the ID from the relevant row. I adjusted the code to use a slightly different method.

The below RLS expressions grab the relevant row of User and store in a variable UserRow based on User email = UPN. Then the Flag and UserID are extracted from this variable.

-- Filter 'Table A'
VAR UPN =
    USERPRINCIPALNAME ()
VAR UserRow =
    CALCULATETABLE (
        User,
        User[User email] = UPN
    )
VAR Flag =
    SELECTCOLUMNS ( UserRow, User[AccessAllPortfolioBoards] )
VAR UserID =
    SELECTCOLUMNS ( UserRow, User[ID] )
VAR Result =
    OR (
        NOT Flag,
        'Table A'[ID] = UserID
    )
RETURN
    Result

-- Filter 'Table B'
VAR UPN =
    USERPRINCIPALNAME ()
VAR UserRow =
    CALCULATETABLE (
        User,
        User[User email] = UPN
    )
VAR Flag =
    SELECTCOLUMNS ( UserRow, User[AccessAllPortfolioBoards] )
VAR UserID =
    SELECTCOLUMNS ( UserRow, User[ID] )
VAR Result =
    OR (
        Flag,
        'Table B'[ID] = UserID
    )
RETURN
    Result

2. To answer your second post's questions:

The RLS "Filter Expressions" above determine whether a given row of 'Table A' or 'Table B' will be visible. Each expression is evaluated in a row context for each row of the relevant table, and if it returns True, then the row is visible, otherwise it is not visible.

The way I have constructed the expressions is to ensure that:

1. If AccessAllPortfolioBoards = True, then
   1. For 'Table A', return True only if the ID matches the User.
   2. For 'Table B', return True for all rows regardless of ID.
2. If AccessAllPortfolioBoards = False, then
   1. For 'Table A', return True for all rows regardless of ID.
   2. For 'Table B', return True only if the ID matches the User.

Taking Mary as an example:

For Table A:

1. Flag = False based on the User table.
2. Therefore the first argument of OR which is NOT Flag = True.
3. This means OR ( ... ) = True for all rows of 'Table A', regardless of the value of ID, meaning all rows of 'Table A' are visible for Mary.

For Table B:

1. Flag = False based on the User table (same as above)
2. This time Flag is not negated, so we are left with a False value for the 1st argument of OR.
3. This means OR ( ... ) is only True when the 2nd argument is True
4. This only happens when 'Table B'[ID] matches UserID (which was retrieved from the User table).
5. This means only rows of 'Table B' matching Mary's ID are visible.

I have attached a modified PBIX, and I suggest testing it out with the different users to verify:

Hope that helps! 🙂

Regards

[OwenAuger]

You're welcome 🙂

Sorry, you're quite right, that last step should have referred to UserEntityFiltered, not UserEntitySource.

Just to ensure the correct file is here, I will replace the uploaded file on the post above.

Glad to help, and all the best!

Regards

[naninamu]

Hi again @OwenAuger  - I had some questions about your example was hoping you could clarify for me.

 1) Take the Table A filter. It should only be filtered where someone has a Portfolio Status of TRUE. Mary has a status of FALSE, thus the FLAG = FALSE. The Table A code says:

VAR Result =
    OR (
        NOT Flag,
        'Table A'[Id] = UPN
    )
RETURN
    Result

So doesn't "NOT Flag" turn Mary's FALSE into a TRUE? And since OR only needs on TRUE to evaluate to TRUE, doesn't RESULT = TRUE. But for Table A it should be false shouldn't it? - as Mary has a status of FALSE and shouldn't be filtering Table A.

2) Which leads me to my second question - how does the table know what to filter on? As in where does it actually tell the table to filter on the UPN? RESULT is just a TRUE/FALSE, which part of the code actually tells Table A or Table B to filter according to the UPN?

Thanks in advance for helping me out! 

[OwenAuger · Accepted solution]

Hi again @naninamu 

Thanks for the clarification, and I'll answer the questions from both your replies.

1. Since the User table has both "User email" and "ID" columns, we can tweak the code to retrieve both the True/False flag and the ID from the relevant row. I adjusted the code to use a slightly different method.

The below RLS expressions grab the relevant row of User and store in a variable UserRow based on User email = UPN. Then the Flag and UserID are extracted from this variable.

-- Filter 'Table A'
VAR UPN =
    USERPRINCIPALNAME ()
VAR UserRow =
    CALCULATETABLE (
        User,
        User[User email] = UPN
    )
VAR Flag =
    SELECTCOLUMNS ( UserRow, User[AccessAllPortfolioBoards] )
VAR UserID =
    SELECTCOLUMNS ( UserRow, User[ID] )
VAR Result =
    OR (
        NOT Flag,
        'Table A'[ID] = UserID
    )
RETURN
    Result

-- Filter 'Table B'
VAR UPN =
    USERPRINCIPALNAME ()
VAR UserRow =
    CALCULATETABLE (
        User,
        User[User email] = UPN
    )
VAR Flag =
    SELECTCOLUMNS ( UserRow, User[AccessAllPortfolioBoards] )
VAR UserID =
    SELECTCOLUMNS ( UserRow, User[ID] )
VAR Result =
    OR (
        Flag,
        'Table B'[ID] = UserID
    )
RETURN
    Result

2. To answer your second post's questions:

The RLS "Filter Expressions" above determine whether a given row of 'Table A' or 'Table B' will be visible. Each expression is evaluated in a row context for each row of the relevant table, and if it returns True, then the row is visible, otherwise it is not visible.

The way I have constructed the expressions is to ensure that:

1. If AccessAllPortfolioBoards = True, then
   1. For 'Table A', return True only if the ID matches the User.
   2. For 'Table B', return True for all rows regardless of ID.
2. If AccessAllPortfolioBoards = False, then
   1. For 'Table A', return True for all rows regardless of ID.
   2. For 'Table B', return True only if the ID matches the User.

Taking Mary as an example:

For Table A:

1. Flag = False based on the User table.
2. Therefore the first argument of OR which is NOT Flag = True.
3. This means OR ( ... ) = True for all rows of 'Table A', regardless of the value of ID, meaning all rows of 'Table A' are visible for Mary.

For Table B:

1. Flag = False based on the User table (same as above)
2. This time Flag is not negated, so we are left with a False value for the 1st argument of OR.
3. This means OR ( ... ) is only True when the 2nd argument is True
4. This only happens when 'Table B'[ID] matches UserID (which was retrieved from the User table).
5. This means only rows of 'Table B' matching Mary's ID are visible.

I have attached a modified PBIX, and I suggest testing it out with the different users to verify:

Hope that helps! 🙂

Regards

[OwenAuger]

Hi @naninamu 

Yes, I would suggest creating a single role which contains RLS DAX expressions for both 'Table A' and 'Table B'.

I have attached a simple PBIX to illustrate.

I have assumed that Id corresponds to the User Principal Name returned by USERPRINCIPALNAME ().

In this example, there is a single Role called DynamicRLS with the following expressions to filter the tables:

-- Filter 'Table A'
VAR UPN =
    USERPRINCIPALNAME ()
VAR Flag =
    LOOKUPVALUE ( User[AccessAllPortfolioBoards], User[Id], UPN )
VAR Result =
    OR (
        NOT Flag,
        'Table A'[Id] = UPN
    )
RETURN
    Result

-- Filter 'Table B'
VAR UPN =
    USERPRINCIPALNAME ()
VAR Flag =
    LOOKUPVALUE ( User[AccessAllPortfolioBoards], User[Id], UPN )
VAR Result =
    OR (
        Flag,
        'Table B'[Id] = UPN
    )
RETURN
    Result

Unfiltered

Vier as user john@test.com (True)

View as user mary@test.com (False)

Please post back if needed.

Regards

[naninamu]

Hi @OwenAuger  - thank you so much for your reply. It was very helpful! 

One thing though is I've realised I've oversimplified my example a little. 

The User table actually contains:

• ID
• User email
• AccessAllPortfolioBoards

While Tables A and B have the [ID] as I stated previously.

So I can check that the USERPRINCIPALNAME() matches the User email in the User table, but I'm having trouble working out how to then grab the corresponding ID in the User Table to filter Tables A or B with.

Would that be some sort of Lookup function? I tried just going 'Table A[ID]' = 'User[ID]' assuming it would just grab the ID of the row filtered by the USERPRINCIPALNAME(), but it didn't work.

Thanks in advance!

Andrew