power bi2

It has quickly become the norm for companies to maximize their data potential by using data in their everyday business processes. And as your data grows and users increase, the need for limiting data to particular individuals or a group of individuals most likely increases as well. Whether you need to limit your data to users due to security reasons or to tailor custom dashboard experiences, row-level security (RLS) in Power BI will empower you to filter your data to the row level and give your users an appropriate and efficient view of what they need.

To implement RLS in your Power BI dashboards follow this easy 3 step process!

1. MANAGE ROLES

Once you’ve made a dashboard you want to publish to the Power BI Service, you can create “Roles”. Here I have a demo dashboard using some sample sales data for the USA.


1


The “Manage Roles” button can be found in the security section of the modeling tab and we will be able to use DAX expressions to filter data for Roles at the row level.


2


When you click on the “Manage Roles” button, a window will appear where you can create roles and define limitations to desired data tables at the row level. In this use case, I am acting as a Superstore Chain that is looking to use RLS to give my regional sales reps a custom view of their sales regions that they can access at the office or on the road with Power BI Mobile. Here I have created some roles that will be used by single sales reps users and one role that will be used by a group of sales reps.


3


DAX expressions can only be written here so that it returns a True/False value.  That means you can use operators such as:

  • = (equals)
  • && (and)
  • || (or)
  • IN (a logical “or” condition)

For example, in the “West” role, I have chosen to limit the “Orders” table to only the Region ‘West’. Here are the other DAX Expressions for my other roles and their explanations if you are new to DAX:

  • Jaqueline Mayberry
    • [Region] = “Central” && [Category] = “Office Supplies” || [Region]=”Central” && [Category]= “Technology”
    • Explanation: Region equals “Central” and the Category equals “Office Supplies” or Region equals “Central” and the Category equals “Technology”
  • Jenny Waite
    • [Region] = “South” && [Category] IN {“Furniture”, “Office Supplies”}
    • Explanation: The Region equals “South” and only the values “Furniture” and “Office Supplies’” in the Category field
  • Jeremy Fleur
    • [Region] = “East”
    • Explanation: Region equals ‘East’
  • Joanne Honeycutt
    • [Region] = “Central” && [Category] = “Furniture”
    • Explanation: Region equals “Central” and Category equals “Furniture”
  • West
    • [Region] = “West”
    • Explanation: Region equals “West”

Once you have your roles created you can press the save button and voila! Your role definitions are saved in your Power BI workbook. You can also test out these roles by clicking on the “View as Roles” button next to the “Manage Roles” button. When I test out the role “Jenny Waite” you can see that it has filtered out all other data except what we have limited her role to see (Furniture and Office Supplies in the South).


4


2. PUBLISH TO POWER BI SERVICE

When you are happy with your dashboard and have validated the roles you can go ahead and log into to your Power BI Pro account in the top right corner of the Power BI desktop window then click on the “Publish” button on the home tab to publish the report to a work space (Make sure that all members you add to the work space are set “Members can only view Power BI content”, otherwise RLS won’t work).


5


6


Once your workbook has published, a dialogue will pop up saying that it was a success and it will give you a link to view the report. Go ahead and click on it and then we can set up the last step in this process.


7


3. ASSIGN MEMBERS TO ROLES

 

To complete the final step we need to assign members to the roles we have created in Power BI desktop in the Power BI Service. After clicking the link provided to open the workbook in the Power BI Service, navigate to the Datasets section and click the “…” to open the dataset menu and click “Security”.


8


From the security menu, you can add members to the roles by entering their email address.


9


Once you’ve added all the appropriate members to the roles, save the settings and feel free to validate the views once again in the Power BI Service by clicking on the ‘…’ in the Row-Level Security menu next to the role name.


10


There you go! With this quick guide you’re up and running with RLS in Power BI giving your team the appropriate level of information when they log in!

 

If you have any questions about implementing RLS in your organization or would like a demo of how Power BI can get you data insights fast, then contact us today!

[maxbutton id=”1″]


matt profile croppedMatt Winter
Analytics Associate
About Matt: Matt leverages Business Intelligence tools with his background in Educational Measurement to empower businesses to make data-driven decisions. Recently, his area of work has included the banking, pharmaceutical, nonprofit, and manufacturing industries.
Matt received a B.A. in Chinese from California State University Los Angeles, an M.A. in Chinese language pedagogy from Indiana University, and an Ed.M. in Applied Linguistics, Second Language Assessment tract, from Columbia University. Matt is certified in Tableau, Spotfire, Power BI, and RapidMiner.