Sharing and access control in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a business intelligence analyst at a retail company.

📋 Request: Your manager wants you to share the monthly sales report with the sales team but restrict access so only managers can see detailed customer data.

📊 Data: You have a sales dataset with columns: Date, Region, Salesperson, CustomerName, Product, SalesAmount.

🎯 Deliverable: Create a Power BI report with sales summary and detailed customer data, then set up sharing and access control so salespeople see only summaries and managers see full details.

Sample Data

Date	Region	Salesperson	CustomerName	Product	SalesAmount
2024-01-05	North	Alice	Customer A	Widget	500
2024-01-12	South	Bob	Customer B	Gadget	300
2024-01-20	East	Charlie	Customer C	Widget	700
2024-02-03	North	Alice	Customer D	Gadget	200
2024-02-15	South	Bob	Customer E	Widget	400
2024-02-25	East	Charlie	Customer F	Gadget	600
2024-03-10	North	Alice	Customer G	Widget	800
2024-03-18	South	Bob	Customer H	Gadget	350
2024-03-28	East	Charlie	Customer I	Widget	900

1

Step 1: Import the sales data into Power BI Desktop.

Use 'Get Data' > 'Excel' or 'CSV' to load the sales dataset.

Expected Result

Sales data table appears in Power BI with columns Date, Region, Salesperson, CustomerName, Product, SalesAmount.

2

Step 2: Create a summary table showing total sales by Month and Region.

Create a new table or use a matrix visual with Rows=Month(Date), Columns=Region, Values=SUM(SalesAmount).

Expected Result

Matrix visual shows total sales for each month and region.

3

Step 3: Create a detailed table visual showing all sales records including CustomerName.

Add a table visual with columns Date, Region, Salesperson, CustomerName, Product, SalesAmount.

Expected Result

Table visual displays all sales rows with customer details.

4

Step 4: Create a role for 'Salesperson' with Row-Level Security (RLS) to restrict detailed data.

In Power BI Desktop, go to Modeling > Manage Roles. Create role 'Salesperson' with DAX filter: [Salesperson] = USERPRINCIPALNAME() or map usernames accordingly.

Expected Result

Role 'Salesperson' restricts data to only rows where Salesperson matches the logged-in user.

5

Step 5: Create a role for 'Manager' with no data restrictions.

Create role 'Manager' with no filters to allow full data access.

Expected Result

Role 'Manager' can see all data including detailed customer information.

6

Step 6: Publish the report to Power BI Service.

Use 'Publish' button in Power BI Desktop to upload the report to your workspace.

Expected Result

Report is available in Power BI Service for sharing.

7

Step 7: Assign users to roles in Power BI Service.

In Power BI Service, go to dataset security settings. Add sales team members to 'Salesperson' role and managers to 'Manager' role.

Expected Result

Users have access according to their roles: salespeople see summaries only, managers see full details.

8

Step 8: Share the report link with the sales team and managers.

Use 'Share' option in Power BI Service to send report access links.

Expected Result

Sales team members can open the report and see data according to their access level.

Final Result

Monthly Sales Report Dashboard

+----------------------+----------------------+----------------------+----------------------+
| Month / Region       | North                | South                | East                 |
+----------------------+----------------------+----------------------+----------------------+
| January              | $500                 | $300                 | $700                 |
| February             | $200                 | $400                 | $600                 |
| March                | $800                 | $350                 | $900                 |
+----------------------+----------------------+----------------------+----------------------+

Detailed Sales Table (Visible to Managers Only):
Date       | Region | Salesperson | CustomerName | Product | SalesAmount
2024-01-05 | North  | Alice       | Customer A   | Widget  | 500
2024-01-12 | South  | Bob         | Customer B   | Gadget  | 300
... (all rows shown)

Salespeople see only the summary matrix without customer names.

✓Sales are highest in East region in March with $900 sales.

✓Managers can see detailed customer data for all sales.

✓Salespeople see only their own sales summarized by month and region.

Bonus Challenge

Add dynamic report page navigation so users can switch between summary and detail pages easily.

Show Hint

Use bookmarks and buttons in Power BI to create navigation links between pages.