Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

ankitpatira

Row Level Security in Power BI

 

In PowerBI.com under Datasets in Left side pane click three dots for your dataset and then click SECURITY.

 

1.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

On Row-Level Security page click Create New Role.

 

2.png

 

 

 

 

 

 

 

 

 

 

 

 

 

Click Continue on warning sign that says if you re-publish this dataset you have to create row level security again.

 

3.png

 

 

 

 

 

 

 

 

 

 

 

 

 

Create roles as per requirements. In this case I have dataset that has some sales figures for different states across Australia so I am going to create roles for each of those states thereby limiting users to only see the sales data for their state.

 

4.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Next step is to add users to those roles created above. Select role on left and under Members tab add users by typing in their email address and click Save. Please note you can only add users who have signed up to PowerBI.com before. If you try add users who have not signed up when you save it will give error.

 

5.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Under Rules tab select table from your dataset and use DAX query to limit access to the rows in that table. Here I am limiting to only those rows in table Sheet1 where State column has value Western Australia. For this post I am keeping it simple but as you can see you can write complex DAX queries to achieve level of security you want.

 

 

Sheet1[State]="Western Australia"

 

 

6.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Once saved next time users of that role access the dashboard they will see the same report with filtered data. Below are the screenshot of the original dashboard and one seen by the user of Western Australia Users role. You may need to download these images and zoom in to be able to see difference in number values for each visuals.

 

7.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

user.png

Comments

@fredrikg @Anonymous

 

Check out this post on using usernames in RLS by Adam Saxton.

 https://powerbi.microsoft.com/en-us/blog/using-username-in-dax-with-row-level-security/

How do you apply more than one rule for a user?  For instance

 

AND(AND([Variable 1] = "XXXX", [Variable 2] = "XXXX"), [Varible 3] = "XXXX") 

 

does not work but 

 

AND([Variable 1] = "XXXX", [Variable 2] = "XXXX")

 

does work.  

 

I have 5 rules I want to implement for a specific user so how can this be done?  Many thanks.  - Steve

@shj997 I don't know if you've already gotten an answer but to make multiple rules with a DAX statement in RLS you need to place '&&' in between the rules. So for example: 

 

[Region] = LOOKUPVALUE(User[Region], [User], USERNAME())&&

[SSC] = LOOKUPVALUE(User[SSC],[User], USERNAME())

 

This works with the dataset that I have loaded with RLS. Let me know if that answers your question. 

@jcox Bear with me, I am beginner in DAX: your example looks fine, as long as a user only has permissions to a single Region and SCC, right? How could this be done if a user could have permissions to multiple Regions and/or SSC:s?

 

/Fredrik

@ankitpatira You have mentioned in one of your answers(ref link below) that with May release we can assign email distro list/ AD groups to the roles. However when I am trying to add the distro list email address, it is not recognising it.

 

https://community.powerbi.com/t5/Service/Dynamic-RLS-based-on-User-Organisational-Hierarchy/td-p/360...

 

Is it because except few, all the other group memebers doesn't have Power BI account?

 

Can you please help me with this?

The Security option is mising in the menu when clicking the elipsis.  Any idea why this woudl be?

@JonathanA RLS is now in the Desktop only. It was removed from the Service.

Are their plans to make RLS available again for the PowerBI service? Is it in Preview for some tenants or has it been pulled completely?  This is very important feature for many customers especially since many Content Packs cannot be imported into PBI Desktop.

@JonathanA Ideally, you should be creating your reports in the Desktop for a number of reasons. It's been pulled from the Service, and you need to build the reports in the Desktop in order to utililze it. Content Packs can still be leveraged, you just start in the Desktop rather than building in the Service.

Anonymous

@ankitpatira Can you please tell us more about AD accounts specific data which should be secured by Login.

Suppose - I have a table which is having 70 rows belongs to me only, this tables has one column which is AD account value but not domain value like "sjain" only but my login id is domain\sjain

So would i need this "domain\" as well in my table or it will authenticate automatically after creating and assigning roles.

Username() is giving domain\sjain

 

@ankitpatira I have create a row level security and given permission to the user and its working fine.

now i have create a group workspace and given that same user view only permission in that case also its working find. but when i give group workspace view edit permission for the same use then row level security is not working.

basically i want to setup row level security and give use permission to edit the report and can create adhoc reporting on the top of same security.

 

Thanks,

@ankitpatira I have created a row level security and given permission to the user and its working fine.

now i have created a group workspace and given that same user view only permission in that case also its working fine. but when i give group workspace view edit permission for the same user then row level security is not working.

basically i want to setup a row level security and give user permission to edit the report and can do adhoc reporting on the top of same security.

 

Hi @wynhopkins @ankitpatira,

 

Could you please help me in below.

 

I have a report in which i have created a role as "UserEmail = userprincipalname()"  and added a security group in which there are multiple users so any user logged in will see only his data.

Now  there is another admin group which can see all data so how can I create the role in same report so that users from one group can see only info related to them and other groups(admin) user can see all info on the report.

 

Currently whenever a user who have not added to any role logged into power bi service(Already have access to the report) then the report appeared as below.Why the user not able to see the visuals?

 

13.PNG

 

When I click on 'See details'

 

14.PNG

 

Thanks,

Jat

 

 

Hi @jatneerjat,

 

You would need to set up another role in PBI Desktop wihtout any filters applied and assign the new admin group to that role

Hi   @wynhopkins,

 

I tried creating the role as [ID] <> "" and added admin group into this role but then also user from admin role not able to view the data on report.

So how to create a role so that user from admin group can see all the data in report.

 

Thanks,

Jat