RLS for IsAdmin not functioning correctly due to lack of all possible values in bridge tables.

[Anonymous]

Hi All, 

I'm not sure whether I am repeating a similiar RLS issue that was faced earlier. My aim is to implement the RLS considering the IsAdmin user.

I have 4 tables, Userdim, Roles, UserRolemapping, Transactional Table.

I have incorporated the following DAX in the userdim table. 

If(

MaxX(

Filter(

'Sales Rep',

'Sales Rep'[Email]=Username())

,'Sales Rep'[Is Manager])=0,

'Sales Rep'[Email]=Username(),

1=1

)

This works fine and filtering the data in my Userrolemappingtable however my userrolemapping table doesn't have  all the possible combinations as that in the transactional table. For Eg. there is a roleid 6 (exists in the source data, but I have not brought into the Roletable or in the userrolemapping table in Power BI due to xxx reasons) and this role 6 has a valid transaction in the transactional table. Since the roleid 6 is not present in the Userrolemappingtable, the Admin user is not able to view this particular record. 

Is there a way to address this scenario? I hope I have briefed the sceario well.

Welcoming any suggestions on this and appreciate the help in advance.

Regards,

Jishnu

[Anonymous]

Hi, The scenario is as follows:

Userdimtable:

UserId, Username,IsAdmin

1, A,1

2, B,0

3,C, 0

4.D.0

RoleUserTable

RoleId, UserId

1,2

2,2

TransactionalTable:

TransactionalId, RoleId, Sum

1,1,10

2,1,15

3,100,50

Now the problem is the Role 100 in the transactional table is not present in the Roleusertable. Hence when I apply the mentioned DAX in the original post, eventhough it identifies the admin it can't show the 3rd row from Transactionaltable.

Hope it is clear now and please help.

Regards,

Jishnu

2,

[amitchandak]

@Anonymous , not sure this can help. But can you create a new row using enter data for RoleId and merge it with your table Roleusertable in edit query mode.

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[Greg_Deckler]

@Anonymous - I guess my question is this. Is the Admin role supposed to see everything?

[Greg_Deckler]

@Anonymous - Not sure I 100% understand but perhaps create a separate role for the admin users that has different logic for the RLS rule that doesn't fall into that trap. Would need more information to be more specific.