Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Proud to be a Super User!
@JohnLow - Did it help? I was curious if I missed any thing
Proud to be a Super User!
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
Proud to be a Super User!
@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.
Please try uploading this file and see if it works
Proud to be a Super User!
@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.
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.
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
)
Proud to be a Super User!
@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]
)
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
Proud to be a Super User!
@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:
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.
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)
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |