Exceptions inRLS in Power BI

[Anonymous]

Hi All,

I am new to Power BI and currently I am working on RLS on Fund. The source here is SAP BW.  I have a user table ("User Security" table ) which has the Users and Fund association, that is which user should see how many number of funds is available in this table.

I have built a security model based on "User Securty" table and it works fine.

Now, there are few exceptions that is few users has access to only only one set of funds, that 9* funds ( they should see all funds starting with 9 i.e 900000 to 9999999). Also, these users are not available in the "User Security" table. 

Can someone please help me with an approach to have this included in the security model that I have built.

Thank you

Poojitha

[Anonymous]

Create a new set of roles with hand crafted security as you have described in your post.  Set the specific users into those roles.

With RLS, you only get the data of the roles you are assigned added together.  So if people are set into 1 set of roles, any other roles you create will not affect their security unless you add them as well.

[Anonymous]

Could you please help with further details.

This is what I have currently set up as RLS model.

These tables consits of the user and fund assignment.

Now I would like to add a user "User A" and assign this user to 9* funds. UserA and all the 9* funds are not available in any of those tables.

Could you please help on how I need to add this. Do I have to create another table?

Please help with further details.

Thank you

[Anonymous]

Your screenshot is the Role editor.  You have created a single role.

You just need to create more roles, using the create button on the left hand side, then set up its rules.

When you publish the report into the Service, you can then assign people to roles under the option "Security" from the datasets menu.

https://docs.microsoft.com/en-us/power-bi/service-admin-rls

[Anonymous]

Thank you for your prompt response.

I have create a role now as 9*, but I am not sure what DAX expression has to be entered as there are no data relevant to 9* in any of the tables below. Can you please provide more details 

Reg, assigning users in the power BI service , I have understood your point

Thank you

[Anonymous]

I'd use the LEFT statement

https://docs.microsoft.com/en-us/dax/left-function-dax

Something like

LEFT('YourTable'[FieldName], 1) = "9"

[Anonymous]

This is how I have created roles.

I have applied this role on the "Source" table which is nothing but the source which will be used in the report layout. After creating this role and publishing this on the service, I can see only the 9* funds in the report.

So on this role, I need to assign only the users who should be seeing this. Hope I am going in the right direction.

Also, Will this role impact the other role which I already have?

Thanks for your prompt response.

[Anonymous]

It sounds like you are on the right path.  Inside Power BI Desktop, you can test those roles using the 'View As" button, which you can see in the Home pane next to the "Manage Roles" button.  This will let you simulate being in those roles you created.

Naturally your "User Logged In" is harder to test given the dynamic code you have created.

Also your filtering will only be as good as the data structure of your model.  If you have set the filter on a table that is not connected to the rest of your structure, you'll only have a partial filter.  In some cases this is good, for example you wouldn't want your Date Table to be filtered if you had one.  Its just worth keeping in mind for your testing.

[Anonymous]

Hi,

I have come across a scenario where a user is now in "User_Security" table, on which I have built a RLS on fund-user where he has access to few funds from that table .Along with those funds he should also have access to all the 9* funds.

As I have created two roles now "User Logged In" and "9*". 

Codes used for each of the roles are as below:

User Logged in - [User Name CWID] = USERNAME()

9* - LEFT('Accounts Payable Details as of &ZMPTU_FYPER&'[Fund/SP.Fund/SP Level 01.Key (Not Compounded)],1)="9"

Now, to have the user get access to RLS and 9*, do I have to edit the code written in 9* to accomodate [User Name CWID] = USERNAME() also? 

Please let me know how this is going to work.

Kindly suggest

Thanks

Poojitha

[Anonymous]

Roles are additional.  Each user will get both roles added together.  If you added this user to both roles, they will get everything each role covers.

[Anonymous]

I am getting the below error when I created both the roles and published to workspace.

(To reiterate : I have a user, who has acess to certain funds for which I have created a role to capture the logged-in user id and then show them all the funds they have access to from the table which I have used to build the security model.

Now I have the same user who should also have access to all the funds starting with 9 for which I have created another role to left( ..)=9 . This fund-user relation is not not avaiable in the table. 

Then in power bi service I have assigned the same user to both the roles and I got this issue)

Please help on resolving this.

[Anonymous]

This is how my model and the roles defined looks like. Can someone please let me know how to fix this?

[Anonymous]

Change your Role's security to be applied to the bridge table and remove the 2 way filter from the bridge to accounts table.

[Anonymous]

Please note that the 2 way filter is not applied from Bridge to Accont table. It is applied from Fund/SP assignemnts to Bridge table. I am still getting the error.

[Anonymous]

You've attempted to set up security on the Assignments table and then have that security relate to Accounts Payable.  Your table relationship structure is incorrect.  Your bridge table is where you want your filtering to be placed into which should allow your combined roles to mesh correctly.

The error relates to both roles attempting to work together, which they can't while your table relationship structure is wrong and your filtering for the User Logged In role is incorrect.

[Anonymous]

Sorry about asking further details since I am completely new to Power BI and I am supposed to complete a project on this.

Could you please help with the below details.

1. Currently I have created "User Logged In" on the assignment table where email=userprinicpalname() to get the user logged in email and then show the funds they have access to

2. 9* on assignments table to get all 9 funds

Are you suggesting to

1. create "USer Logged in" on bridge table ? (Do I have to create a email field in this table and use it?)

2. and the 9* should also be on bridge table

3. Remove both direction and Uncheck security option.

Kindly help on how I can proceed.

Thanks

Poojitha

[Anonymous]

Its important to understand what a Star Schema is and why you need to use it in Power BI.  Your bridge table was created correctly to enable a star schema to be possible.  The problem is that once you created the bridge table, you then used it incorrectly.

Security and filters are applied based on the arrows.  Dual direction arrows, except when its 1:1, are generally an indicator that you have done something wrong or going down the wrong path.  My expectation is that you originally had just the single filtering but you couldn't get your filtering to work.  This would be because you've been applying your filtering on the Assignments table and expecting it to flow into your Accounts table.

Your bridge table is acting as higher order table.  This table is where you should be applying your Assignment related filtering to.  Doing so will ensure the filtering goes down to both Accounts and Assignments.

At present, any filtering on Accounts has no impact on Assignments.  I assume this is what you are trying to do as there isn't sufficient logic to restrict Assignments based on Accounts.  However it is possible to restrict Accounts based on the Assignment (which is where the bridge table comes in).  In reality what you are calling a bridge table is really just a list of AuthObjs.  Your filtering for Assignments is AuthObj based, hence it make sense to have a table of them and filter from there.

[Anonymous]

Thank you for the detailed information. I have made few changes in the model and the bi directional arrows but now the data does not seem to be showing correctly. It shows all the data when executed, looks like I have missed somthing again.

[Anonymous]

The model changes you have made are incorrect.  You already had the only bridging table required.  You only need the 3 tables you showed in your original post.  You only needed to make 2 changes:

1. Remove the 2 Way relationship between your bridge table and the assignment table
2. Change all of your filter statements that refer to the assignment table, and have them refer to the equivilent column but inside your bridge table.

[Anonymous]

9* role seem to work fine but the 'user logged in' role does not work.

I have not applied "user logged in" role on the bridge table since I do not have the username field in the bridge table.

Bridge table now has only the unique fund numbers to enable a join between Accounts table and bridge table.

Adding username field in the bridge table will cause many to many relation between accounts and bridge table.

[Anonymous]

Sounds like this is getting to your issue then.  Sounds like you need to rethink your data.

Based on your original Schema, AuthObj is your most important aspect.  You've essentially based all of your relationships around this.

If each 1x AuthObj is linked to 1 email only, everything can be fixed by putting the email address in that bridge table.

If each 1x AuthObj is linked to 2+ emails as well as 2+ accounts, you will have data integrity problems if you are trying to filter anything in Accounts or Assignments and want them to filter the other table (Accounts or Assignments).