Bird
Raised Fist0
Power BIbi_tool~15 mins

Testing RLS in Desktop in Power BI - Real Business Scenario

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Scenario Mode
👤 Your Role: You are a Power BI developer at a retail company.
📋 Request: Your manager wants you to set up Row-Level Security (RLS) in the Power BI Desktop report so that sales managers only see data for their own regions.
📊 Data: You have a Sales table with columns: SalesID, Region, SalesAmount, and SalesDate. You also have a Users table with columns: UserName and Region, mapping each user to their allowed region.
🎯 Deliverable: Create a Power BI report with RLS configured. Test the RLS roles in Power BI Desktop to verify that users see only their region's sales data.
Progress0 / 6 steps
Sample Data
SalesIDRegionSalesAmountSalesDate
1North10002024-01-15
2South15002024-01-20
3East12002024-01-25
4West13002024-02-05
5North11002024-02-10
6South14002024-02-15
7East12502024-02-20
8West13502024-02-25

UserNameRegion
alice@example.comNorth
bob@example.comSouth
carol@example.comEast
dave@example.comWest
1
Step 1: Load the Sales and Users tables into Power BI Desktop.
Use 'Get Data' to import both tables from your data source or enter data manually.
Expected Result
Both tables appear in the Power BI Fields pane.
2
Step 2: Create a relationship between Users[Region] and Sales[Region].
In the Model view, drag Users.Region to Sales.Region to create a one-to-many relationship.
Expected Result
Relationship is active and shown in the model.
3
Step 3: Define a new role for RLS in Power BI Desktop.
Go to Modeling > Manage Roles > Create a role named 'RegionRole'.
Expected Result
Role 'RegionRole' is created but needs a DAX filter on Sales table.
4
Step 4: Apply a DAX filter on the Sales table for the role to restrict data to the user's region.
Use this DAX filter on Sales table: Sales[Region] = LOOKUPVALUE(Users[Region], Users[UserName], USERPRINCIPALNAME())
Expected Result
Role filters Sales data to only the region assigned to the logged-in user.
5
Step 5: Test the role in Power BI Desktop.
Go to Modeling > View as > Select 'RegionRole' and enter a test user email like 'alice@example.com'.
Expected Result
Report shows only sales data for the North region.
6
Step 6: Repeat the test for other users to verify RLS works correctly.
Test with 'bob@example.com', 'carol@example.com', and 'dave@example.com' in 'View as' mode.
Expected Result
Each test user sees only their assigned region's sales data.
Final Result
Power BI Report - Sales by Region with RLS

+----------------+----------------+
| Region         | Total Sales    |
+----------------+----------------+
| North          | 2100           |
| South          | 2900           |
| East           | 2450           |
| West           | 2650           |
+----------------+----------------+

* When testing as alice@example.com, only 'North' sales rows appear.
* When testing as bob@example.com, only 'South' sales rows appear.
✓RLS correctly restricts sales data to the user's assigned region.
✓Testing in Power BI Desktop 'View as' mode helps verify security before publishing.
✓Each sales manager sees only their region's sales, ensuring data privacy.
Bonus Challenge

Extend the RLS to allow regional managers to see their region plus all sales from the 'East' region.

Show Hint
Modify the DAX filter to include Sales[Region] = LOOKUPVALUE(Users[Region], Users[UserName], USERPRINCIPALNAME()) || Sales[Region] = "East"

Practice

(1/5)
1. What is the main purpose of using View as Roles in Power BI Desktop?
easy
A. To preview how data looks for different user roles with Row-Level Security applied
B. To create new data tables in the model
C. To export reports to PDF format
D. To refresh data from the source

Solution

  1. Step 1: Understand the function of 'View as Roles'

    This feature lets you simulate the data view of different security roles without publishing the report.

  2. Step 2: Identify the purpose in context

    It helps verify that Row-Level Security (RLS) filters are working correctly by showing what data each role can access.

  3. Final Answer:

    To preview how data looks for different user roles with Row-Level Security applied -> Option A

  4. Quick Check:

    View as Roles previews RLS effects [OK]
Hint: Use 'View as Roles' to test RLS before publishing [OK]
Common Mistakes:
  • Confusing 'View as Roles' with data refresh
  • Thinking it creates new tables
  • Assuming it exports reports
2. Which of the following is the correct way to define a role filter in Power BI Desktop using DAX?
easy
A. Sales[Region] = "West"
B. FILTER(Sales, Sales[Region] = "West")
C. Sales[Region] == "West"
D. Sales.Region = 'West'

Solution

  1. Step 1: Recall DAX filter syntax for roles

    Role filters use a Boolean expression like Table[Column] = "Value" to filter rows.

  2. Step 2: Evaluate each option

    Sales[Region] = "West" uses correct DAX syntax with single equals and double quotes. FILTER(Sales, Sales[Region] = "West") is a function, not a direct filter expression. Sales[Region] == "West" uses double equals which is invalid in DAX. Sales.Region = 'West' uses dot notation and single quotes, which is incorrect.

  3. Final Answer:

    Sales[Region] = "West" -> Option A

  4. Quick Check:

    DAX role filter uses single equals and double quotes [OK]
Hint: Use single equals and double quotes in role filters [OK]
Common Mistakes:
  • Using double equals (==) instead of single equals (=)
  • Using dot notation instead of bracket notation
  • Using FILTER function inside role filter
3. Given a role with filter Customers[Country] = "USA", what will COUNTROWS(Customers) return when viewing as this role if the Customers table has 100 rows with 30 from USA?
medium
A. 100
B. 70
C. 30
D. 0

Solution

  1. Step 1: Understand the role filter effect

    The role filter limits visible rows to those where Country = "USA", so only 30 rows remain visible.

  2. Step 2: Apply filter to COUNTROWS

    COUNTROWS counts only visible rows under the role filter, so it returns 30.

  3. Final Answer:

    30 -> Option C

  4. Quick Check:

    Role filter limits rows, COUNTROWS returns filtered count [OK]
Hint: COUNTROWS respects RLS filters, returns visible rows only [OK]
Common Mistakes:
  • Ignoring role filter and returning total rows
  • Confusing filtered out rows with visible rows
  • Assuming COUNTROWS returns zero if filtered
4. You defined a role with filter Orders[Amount] > 1000 but when testing with 'View as Roles', all data is visible. What is the most likely cause?
medium
A. The role filter syntax is incorrect and ignored
B. The Orders table has no rows with Amount > 1000
C. The 'View as Roles' feature is disabled in Power BI Desktop
D. You did not assign the role to any user

Solution

  1. Step 1: Check role assignment

    In Power BI Desktop, roles must be assigned to users in the service, but 'View as Roles' simulates roles without user assignment. However, if you select 'View as Roles' and no role is selected, no filter applies.

  2. Step 2: Verify role filter syntax and data

    The syntax Orders[Amount] > 1000 is correct. If data had no matching rows, filtered data would be empty, not all visible.

  3. Final Answer:

    You did not assign the role to any user -> Option D

  4. Quick Check:

    Role must be selected in 'View as Roles' to apply filter [OK]
Hint: Select the role in 'View as Roles' to apply filters [OK]
Common Mistakes:
  • Assuming syntax error when filter is correct
  • Not selecting role in 'View as Roles' dialog
  • Thinking 'View as Roles' is disabled by default
5. You have two roles: RoleA filters Sales[Region] = "East" and RoleB filters Sales[Region] = "West". How can you test combined access for a user assigned to both roles in Power BI Desktop?
hard
A. Create a new role with a filter combining both regions and test it
B. Use 'View as Roles' and select both RoleA and RoleB to see combined data
C. Assign the user only to RoleA and test, then switch to RoleB
D. You cannot test combined roles in Power BI Desktop

Solution

  1. Step 1: Understand role combination in 'View as Roles'

    Power BI Desktop allows selecting multiple roles simultaneously in 'View as Roles'. The filters combine with OR logic, showing data accessible by any selected role.

  2. Step 2: Apply multiple roles selection

    Selecting both RoleA and RoleB will show sales data from both East and West regions combined.

  3. Final Answer:

    Use 'View as Roles' and select both RoleA and RoleB to see combined data -> Option B

  4. Quick Check:

    Multiple roles combine filters with OR in 'View as Roles' [OK]
Hint: Select multiple roles in 'View as Roles' to test combined access [OK]
Common Mistakes:
  • Thinking roles combine with AND logic
  • Creating new roles unnecessarily
  • Believing combined roles cannot be tested
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard