In most organizations, security is not just a report-level decision. Organizations want more granular decisions, such as whether a sales executive can only see his or her own data. Another example is the ability for a teacher to see his or her own students, but the school's principal can see all the teachers at their school and the school board members can see all of the data. This level of granularity is quite possible in Power BI, but will require some thought ahead of time on how to lay the data out.
To show an example of this, we'll need to go back to the Power BI Desktop and open Chapter 5 - Visualizing Data Completed.pbix from a previous chapter's example; this file can be downloaded from this book's web page at http://packtpub.com. The goal of this example is to ensure that United States sales managers can only see US sales, and likewise for Australian sales managers. We'll only use two countries in our example, but the same example can apply to the entire world, and can be expanded to be made more dynamic.
To create this type of automated filter based on your user's credentials, you'll need to use DAX language snippets. Open the Power BI Desktop and click Manage Roles from the Modeling ribbon in the report. Then, click Create to make a new role called US. Then, select Sales Territory as your table to filter on and click Add Filter | [Sales Territory Country], as shown in the following screenshot. This will create a stub of code in the Table Filter DAX Expression box that shows [Sales Territory Country] = Value. Simply replace Value with United States, and your first role is created. Do the same for Australia to complete the example:
Now that we've created the two rules, let's test them out. The Power BI desktop will not automatically filter the data for you, since you have access to the underlying data anyway, but it can be used to test it. Click View as Role from the Modeling tab and select the role you wish to test. You'll notice after you click on Australia, for example, that every report element on each report page filters at that point to only show Australian data. Power BI Desktop also warns you that you're filtering the data, and that you can click Stop Viewing to stop viewing as the role. Once you're ready to see what you've done on the Power BI service, publish to your Power BI account and open the report there.
Navigate to the dataset matching your report and select Security. You can then select each role and type the email address of each member of that role. Click Add and then Save to start using the role, as shown in the following screenshot. You can also add groups (such as your Australian Employee group) to this role if you have one created already in Office 365's directory. After clicking Save, members of that role will only see their own data in dashboards, reports, and any new reports that they build from the dataset:
