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

[naninamu]

Hi @OwenAuger  - I have been using your advice and techniques to remodel the backend of some of my reports, so a huge thanks. I had one thing I was wondering if you could help with. In one of my reports, I managed to refine the tables down so that the model looks like this:

What I wanted to do was create a table visualisation featuring the Entity Name, Portfolio Name, Member, Gender and Qualifications. Problem is it wouldn't allow that to happen - whenever I put in fields from different tables it returned an error. 

However, when I adjusted my model to look like this it worked. Problem is I know many to many isn't good - I thought doing it with the bridging table would be the correct way to go about things. Would you have any insight into which way is better, or why my first method didn't work? Thanks! 

UPDATE - I've worked out that the many to many version is actually not giving me correct result (after some labourious manual checking). Problem is my "proper" version with the bridging table won't let me build my required table at all... any suggestions? Thanks!

[OwenAuger]

Hi again @naninamu 

Glad to have been able to help a bit so far 🙂

To make that work, I would suggest:

1. Change the relationship between GovernmentEntityPosition and GovernmentPositionBridging to bidirectional (Cross filter direction = "Both")
2. Create a measure that performs some sort of aggregation on PositionApplicant, and add that to the visual. This could simply be a row count like 
   Application Count = COUNTROWS ( PositionApplicant )
   If you wanted to, you could narrow the column of the table containing that measure so that it's invisible, and switch off "Text wrap" for both Values & Column Headers.
   

This measure is needed as a technicality to force Power BI to determine which combinations of values produce a nonblank measure. It is needed in this case because of the many:1:many relationship path.

Does something like this work?

Regards

[naninamu]

Hi @OwenAuger  - thanks for getting back to me! 

I ended up slightly altering my model  - I actually did Step 1 as you suggested, but my second step wasn't quite as elegant but seems to be producing the correct results.

When I get a moment, I will try out your suggestion to add to me learning! 

Many thanks again 🙂

[naninamu]

Hi @OwenAuger  - I've discovered that an Entity can sit under multiple portfolios. Can I use a Many to Many relationship betwee the UserEntity and Positions tables? If not, what bridging table do you think I should use, as using the existing EntityTable still means a Many to Many retionship on either side. 

Thanks!

[OwenAuger]

Hi again @naninamu 

I generally prefer to use bridge tables rather than have any many-to-many relationships in the model.

In this situation, I'm thinking I would:

• Remove Portfolio from EntityTable, and maintain one row per EntityID in EntityTable.
• Then create an EntityPortfolio bridge table.
• Optionally add a Portfolio dimension table if there are any descriptive attributes of Portfolios to be included.

This would still ensure that each User sees the correct Entities, and allows Entities to be grouped or filtered by Portfolio.

Would this work for you?

[naninamu]

Thanks @OwenAuger  - I'll give that a go! As part of my final model, I need to be able to filter on both Porfolio and Entity - should I make that relationship to the Portfolio table multidirectional so that the Portfolio slicer changes according to what is selected in the Entity dropdown?

[OwenAuger]

No worries 🙂

Good point, yes, if Portfolio should be crossfiltered by Entity (which does make sense), then make that relationship bidirectional too 🙂

[naninamu]

Excellent thanks for that! 🙂

[naninamu]

Thanks for such a detailed reply @OwenAuger , I really appreciate it. I will spend some time going over and understand it, so I'll be well armed for next time! Thanks again 👍

[naninamu]

Hi @OwenAuger  - I was hoping I could call on your advice once more.

Table A and Table B are both connected to Table C  - Table C is filtered by Table A or Table B depending on which of those is filtered (accoreding to the AccessAllPortfolioBoards = True/False logic).

The issue is, upon testing, it seems that that tables are simultaneously filtering Table C no matter what the AccessAllPortfolioBoards value is  - I'm guessing this is because for the table where all lines are displayed, we haven't left it untouched as such, those displayed rows are acting as a filter (this is my guess!)

Is there anyway around this? Essentially, if the value = TRUE, how do I make Table A the filter, and Table B does NOT act as a filter?

Thanks in advance! Andrew

[OwenAuger]

Hi again @naninamu 

Interesting 🙂

The closest solution I can think of is:

1. Make the relationships inactive between Table A -> Table C & Table B -> Table C.
2. Use DAX to enable the appropriate relationship (based on the same logical test as used in RLS).

For convenience, you could create a calculation group to enable the relationship for all measures at once. I'm assuming that it's sufficient to enable the correct relationship in measures.

Could you give a bit more detail on what columns the relationships are between, and what behaviour you expect in the final report?

That would help me mock up an example (if that's useful).

Regards

[naninamu]

Hi @OwenAuger  - again thank you for taking the time to reply.

Here is what all the tables in my model look like. Note I will use the actual names of the table now as I found myself getting confused using Table A and Table B!

The User table isn't connected to anything, but references the USERPRINCIPALNAME function.

Essentially, Portfolios are made up of entities.

If your status = TRUE, you can access all the Entities within the Portfolio (the PortfolioEntityTable details this)

If your status = FALSE, you can only access the Entites detailed in UserEntity.

So, below are some mocked up tables. 

Expected Behaviour:

•  If User 100 logs in, their FLAG should be TRUE. This would mean they look at the UserPortfolio table, and can access Portfolio #1. This Portfolio has 2 Entities within it - 1111 and 1112. THerefore the VacantPositions they are allowed to view would be Pos1, Pos2, Pos3, Pos4, Pos5, Pos6, and Pos7.
• If User 300 logs in, their FLAG should be FALSE. This would mean they can only view a limited number of Entities - in this case Entity 1111. This would mean they can only see VacantPositions Pos1, Pos2 and Pos3.

Thanks in advance - I have thought of the way you suggested with inactive relationships, just not sure how to do that (I think I actually read somewhere is can't be done from DAX in RLS, but hoping that's wrong!)

Also I'm not familiar with Calculation Groups, so if you do use that if you could explain it extra simply that would be great 😄

Thanks once again. 👍

[OwenAuger]

Hi again @naninamu Thanks for the great detail and explanation of the model/requirements!

After looking at this, I'm actually thinking we we can greatly simplify things 🙂

Also, I agree that, given the requirements, an inactive relationship method is not going to work since, as you've mentioned, inactive relationships aren't compatible with tables involved in RLS. 

Observations:

• EntityTable1 & EntityTable2 are identical, and are both there at the moment is to allow the alternative filtering paths.
• The current two "options" for RLS, with each user's filter based on either UserPortfolio or UserEntity, ultimately end up mapping each user to a set of Entities.
  For example, UserID 100 maps to Portfolio 1, which maps to Entities 1111 & 1112.

Recommended changes:

• Create a UserEntity table that directly maps each User to the relevant Entities (whether directly or based on Portfolio). This table would replace UserPortfolio & UserEntity.
• Create a 1:many relationship between USER and UserEntity.
• Create a many:1 relationship between UserEntity and EntityTable (bidirectional).
• Include just a single EntityTable.
• Apply RLS filter on USER table only, with a simple condition:
  User[Email] = USERPRINCIPALNAME().

Data model would look like this:

USER table:

UserEntity table:

Note: Relationship between UserEntity & EntityTable must be bidirectional and have "Apply security filter in both directions" enabled.

Please try out the attached PBIX and let me know if this is going to work for you 🙂

Regards

[naninamu]

Thanks @OwenAuger - your solution makes sense to me! Certainly a lot more elegant than the route I was going down.

How do I actually create the UserEntity table though? Obviously in real life there are a lot of Users and Entities, plus I am unsure how to get all the Portfolio/Entity stuff into one table.

I'm assuming it's not a matter of manually typing up a new table 😁

Thanks for your help!!

[OwenAuger]

No worries 🙂

If you are starting from the original UserEntity and UserPortfolio tables, then you should be able to automate this.

The steps I used to create the new UserEntity table in Power Query are:

1. Take the original UserPortfolio table.
2. Left Join with PortfolioEntity based on PortfolioID.
3. Expand EntityID from PortfolioEntity, and remove PortfolioID.
4. Append (union) with the original UserEntity table.

You can follow these steps in the PBIX attached to my previous post. You could equally carry out equivalent steps in the source database if that's an option.

Please post back if needed 🙂

Regards

[naninamu]

Hi again @OwenAuger ,

Thanks for the tutorial - I always forget PQ is my friend in situations like that! 

OK, there's a slight complication. Delving into the tables (we have inherited these tables, I have to build the solution over the top) it appears that a UserID can appear in both the UserEntity and the UserPortfolio tables - I guess the True/False can change so they need to be set up for both occurances.

I don't think your solution handles this - is there some way we can read in the True/False status to our new table and relate that to what is in the User table to filter out the correct VacantPostiion rows?

Cheers, ANdrew

[OwenAuger]

No worries 🙂
The way I have set up the sample dataset, if a UserID appears in both UserEntity and UserPortfolio, they would be able to see the union of the Entities defined by those two tables.

Would this be the way you want it to work?

As an example, I've attached a new PBIX with User 500 who can see Entity 1112 and Portfolio 2.

This translates to Entities 1112, 2222 & 2223.

The AccessAllPortfolioBoards column is ignored by this method. All the matters is the EntityID values that end up in the UserEntity table.

UserEntityOriginal

UserPortfolio

UserEntity final table:

Let me know if this is how you would want it to work or some other way 🙂

Regards

[naninamu]

Hi @OwenAuger  - unfortunatley we can't ignore that AccessAllPortfolioBoards flag - it literally is saying if the person can view ALL the Entities in the Portfolio (True), or only SOME of them (False).

Therefore the Union won't work. For your User 500, let's say their Flag = False, then they shouldn't see anything in Portfolio 2, only the Entity in the other table, 1112 (which probably wouldn't happen in real life as 1112 isn't in Portfolio 2, but I know what you mean).

So yes we def need to observe that Flag. That's why I was thinking can we add a column to the new table to say if the Entity is from the Portfolio or Entity table originally, and then somehow use this to filter which rows are shown, based on the Flag in the User table?

Thanks! Andrew

[OwenAuger · Accepted solution]

Thanks for the explanation @naninamu , and sorry for misunderstanding. I realise now I slightly misread your previous post!

So if I've understood it correctly, a given user can either:

1. See Entities based on the UserPortfolio table (AccessAllPortfolioBoards = TRUE); or
2. See Entities based on the UserEntity table (AccesAllPortfolioBoards = FALSE).

(This is essentially the requirement as stated in your first post 🙂 )

To implement this, we can tweak my last model by ensuring a given user is included in only one of the two tables before unioning.

I have slightly reorganised the queries in Power Query to clarify the steps.

The source tables are now:

• UserPortfolioSource
• UserEntitySource

These are then filtered by 

1. Joining with USER table
2. Expanding AccesAllPortfolioBoards
3. For UserPortfolio, filtering AccesAllPortfolioBoards = TRUE
4. For UserEntity, filtering AccesAllPortfolioBoards = FALSE

The resulting tables are

• UserPortfolioFiltered
• UserEntityFiltered

These are then unioned into UserEntity.

This ensures a given user appears in only one of the component tables, which are then unioned.

I have updated with User 500 appearing in both tables (with Portfolio = 2, Entity = 2223), but having AccesAllPortfolioBoards = FALSE, so can only view Entity 2223.

This way, we can keep the same simple RLS filter expression.

Let me know if this is the logic you expect 🙂

[naninamu]

Hi @OwenAuger  - I think that's perfect, thank you so much! Looking at how you've gone about this will really help me going forward with similar problems, as I come from an Excel background but am not that familiar with manipulating columns. I will try to translate what you have done into my model, but it looks like that's exactly what I need.

One minor thing, which may be my misunderstanding - you said that UserPortfolioFiltered and UserEntityFiltered are unioned into UserEntity.

When I look at the last step of UserEntity however, it seems that UserEntitySource is apended. Although in this case the result is the same, can I just confirm if that's an error or if I'm misunderstanding something?

You assistance and time taken to help me is much appreciated. Thanks!!