Row Level Security Question

[JohnLow]

Hi,

I'm new to RLS and need a little help. I've 3 tables Users, Network and Sales. Users has a relationship to Network which in turn has a relationship to Sales. Users have 3 roles: Network User, Admin and Finance. Admin and Finance should be able to see everything in the Sales table whereas the network user should only see what they have been assigned via the network table. I'm a little unsure as to whether I need to create a seperate role for all 3 roles. I've created a network role which seems to be function ok but would like that the finance and admin role override the network role and am unsure how to do that. 

I've included the the pbix file here. 

Thanks for the support. 

[sayaliredij]

@JohnLow  - Did it help? I was curious if I missed any thing

[JohnLow]

@sayaliredij , great, thanks for your support.

[sayaliredij · Accepted solution]

Hi @JohnLow 

Could you try applying a security filter

I tried at my end and it works

For Finance user - everything is visible and for network user - only data related to him

[sayaliredij]

@JohnLow 

Could you check this file 

sorry I missed that the connection between Network and sales was one way

I enabled the following setting and then it worked

[JohnLow]

@sayaliredij , thanks again. I just realised that the relationship between network and sales is many to many. I should have been clearer in my model. I've attached the file again. Sorry for the confusion. 

File 

[sayaliredij]

Please try uploading this file and see if it works 

[sayaliredij]

@JohnLow  It seems DAX provided by you is not handling all the scenarios. I think we don't need to add 2 DAX in sales and user

I added only one DAX - 

and when I view it as 

I see all the data 

also check the file 

[JohnLow]

@sayaliredij Thank you for the support but this doesn't handle the scenarios for the network users. For example, Justin below (UserID 4) should only see the first 2 rows of the table below, he shouldn't see the row with network name, Sparkling Socials. 

[JohnLow]

Hi, could anyone help me a little further here? The RLS works fine for Network users, but all other users don't see anything and they should see everything. Here's the file: FILE 

Thanks again. 

[sayaliredij]

In case you don't want to use 2 roles

you can use the following DAX for the user table 

If(MaxX(
Filter(
Users,
Users[Username] = Username())
,Users[Role])= "Network user",
Users[Username] = Username(),
1=1
)

[JohnLow]

@sayaliredij Thanks. I have a similar DAX statement for my network role in the pbix file above. This works ok. I'm just wondering do I need another role. Could you have a look at the pbix file attached please?

[NetworkID] IN
SELECTCOLUMNS (
    FILTER (
        Network,
        Network[UserId]
            = LOOKUPVALUE ( Users[UserID], Users[UserName], USERPRINCIPALNAME() )
    ),
    "NetworkID", [NetworkID]
)

[sayaliredij]

I think one role with DAX above should be sufficient. I checked pbix file attached and tried with only one role and viewing it as different username as mentioned in the Users Table  and I was getting expected result 

[JohnLow]

@sayaliredij, I'm not sure. If I add for example, Leah, a non network user to the network group online, she doesn't see everything. She should see everything. 

If I test with Leah, a non network user, she doesn't see anything in the table:

[JohnLow]

Ideally what I would like is one role. I would create a new column in the Users table with an override flag. 

OverRideFlag = IF(
	ISERROR(
		SEARCH("Network", Users[Role])
	),
	"Y",
	"N"
)

 The DAX would then say, if User is a network user(override flag = no) follow the DAX in message 4  above, if not (override flag = yes) access everything in the Sales table. I could then add the everyone group to the powerbi online service and then it would filter automatically? The only manual work I would have to do is to share the report. 

Would be great if you could help me with the DAX, thanks again.

[sayaliredij]

You can create 2 roles 

1. Admin Role - where you can put all the people who would have access to all numbers 

2. Network role with DAX Formule = [Username] = USERNAME()

In power bi service while defining RLS configuration for Admin Role - you can put all the people from administrator and finance and for other network role - people from network departments (all other people)