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

Hope something can be done for the desktop version as well

Hi Ankit

 

How did you handle checking that the filters had worked, did you have to log in as a particular user?

 

 

Hi Wyn, I had this dashboard shared with internal user. So after implementing RLS, I just asked him to access the dashboard.

Ah, OK.  I've flagged this as request

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13260438-preview-impact-of-row-le...

 

Please vote!  🙂

 

 

On another note, I'm actually in discussions with the PowerBI support guys as my Row Level security is causing me isues.  Applying a rule to one user causes all other user's dashboards to blank out.  Very odd.

Hello Folks, you can also check our webinar conducted on RLS in Power BI. Link provided below:

 @wynhopkins Sure will do (voting).

 

I think it is expected behaviour as when you implement RLS you're saying you want to control access to your dataset. So then when you apply RLS for that user his access is controlled but you haven't setup anything for other users and so they all see blank.

 

I think by design, if you don't have RLS implemented everyone can see, when you implement RLS then you have to manage access for each users you want report to see. And it make sense otherwise it introduces ambiguity where one user has controlled access while others (that you may have forgotten to include in a role) can see everthing.

 

I've submiited this idea along the lines of above to have better design. Smiley Happy

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13573167-row-level-security-and-p...

 

 

 

Hi Ankit

 

That is indeed what is happening.  I can see the logic, just didn't realise it worked that way. 

If you wanted someone to see an unrestricted data set would you create a rule called "All States" and use something like [State] <> ""

 

 

 

Anonymous

It is a most welcome feature!

 

However it raises the question too: REST API. It would make an awful lot of sense to automate the management of the roles. E.g. in an enterprise grade business solutions users come and go, and they are usually already managed in roles somewhere. Synchronizing the custom roles of the business application with the roles for the datasets in Power BI is an extra management task. Which demands automation, otherwise it will be a nightmare to manage the roles of users in yet another system manually. So much that I opened a new idea for it here:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13616925-rest-api-for-managing-ro...

 

Please vote for it if you find that missing feature useful.

This is better than nothing, but I am waiting for when the current user's identity can be used in the DAX-expression, like using the EffectiveUsername in SSAS.

That way, we could model our own roles/security and not having to create "static" roles in Power BI.

 

For instance, the example you give with the nurse having access to a patient's records isn't really doable at this time - that would require maintaining individual Power BI roles for each patient.

 

 

Anonymous

Indeed, this solution does not replace the necessity of having user context accessible in DAX. That would help tremendously for lots of cases in my business too, however I understand that it is not implemented by design. It is a pitty.

 

But having these static groups is a great help when you need to deal with e.g. organizational units, where you would like to allow the users to access data in the reports that concerns them, and nothing else. It's just a pain to manage it, as they are static at the moment. If there would be a Rest API to manage the roles, then you could implement the per patient scenario too: using a tonne of individual groups. Which is impossible to maintain mannually, but with the currently missing Rest API it would be manageable. Awkward, but feasible.

Definitely awkward 🙂

Would be interesting to know if user context in the DAX is something which is being worked on. If so, I should wait for that, instead of spending time on static groups.

 

Anyone knowing anything about that?

Anonymous

I join the question: is there any news of making the logged in user information accessible to DAX? That would allow user context based queries. Which is important not only for security reasons, but for user experience in general too!

@fredrikg @Anonymous I was pointing to the fact that if large number of patients are classified into a group let's say 'Clinic ABC Patient's' role and then nurse as users are added to thsoe roles then you can have that nurse only accessing data for his/her patients. You don't necessarily need to create individual roles for each patient as often in real world scenario you will find single nurse looking after many different patients.

 

But agree user context accessible would also be very useful. 🙂 

 

 

Anonymous

@ankitpatira Agreed. For the nurse vs. patient type of data model this grouping could work just fine. Just managing it is a challenge without an API to use for automation.

This solution has very limited usefulness as it is not scalable.  Attempting to manage users in roles in yet another non-centralized repository quickly becomes a nightmare.  BTW - This is not row level security, this is role level security.  Row level security is achieved when data is filtered based on the attributes of the user, not the attributes of a role.