Why RLS protects sensitive data in Power BI - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to define a role that filters data by region.

Power BI

FILTER('Sales', 'Sales'[Region] = [1])

Drag options to blanks, or click blank then click option'

A'North'

B'South'

C'East'

D'West'

Attempts:

3 left

💡 Hint

Common Mistakes

Using a region name without quotes

Using a column name instead of a value

The filter expression uses the region 'South' to restrict data visible to that role.

2fill in blank

medium

Complete the DAX expression to create a dynamic filter for the current user.

Power BI

FILTER('Employees', 'Employees'[UserName] = USERPRINCIPALNAME() && 'Employees'[Department] = [1])

Drag options to blanks, or click blank then click option'

A'Marketing'

B'HR'

C'Finance'

D'IT'

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting quotes around the department

Using a wrong department name

This filter restricts data to employees in the Finance department matching the current user.

3fill in blank

hard

Fix the error in the RLS filter expression to correctly restrict data by country.

Power BI

FILTER('Orders', 'Orders'[Country] = [1])

Drag options to blanks, or click blank then click option'

A'USA'

BUSA

C"USA"

DOrders[Country]

Attempts:

3 left

💡 Hint

Common Mistakes

Using unquoted text

Using double quotes instead of single quotes

The country name must be a text string enclosed in single quotes for the filter to work.

4fill in blank

hard

Fill both blanks to create a role that filters sales by region and year.

Power BI

FILTER('Sales', 'Sales'[Region] = [1] && 'Sales'[Year] = [2])

Drag options to blanks, or click blank then click option'

A'East'

B'West'

C2023

D2022

Attempts:

3 left

💡 Hint

Common Mistakes

Putting year in quotes

Using wrong region name

The filter restricts data to the East region and the year 2022.

5fill in blank

hard

Fill all three blanks to create a DAX filter that restricts data by user, department, and active status.

Power BI

FILTER('Employees', 'Employees'[UserName] = USERPRINCIPALNAME() && 'Employees'[Department] = [1] && 'Employees'[IsActive] = [2] && 'Employees'[Role] = [3])

Drag options to blanks, or click blank then click option'

A'Sales'

BTRUE()

C'Manager'

DFALSE()

Attempts:

3 left

💡 Hint

Common Mistakes

Using FALSE() instead of TRUE()

Omitting quotes around text values

This filter allows only active Sales department managers matching the current user.

Practice

1. What is the main purpose of Row-Level Security (RLS) in Power BI?

easy

A. To speed up data refresh times

B. To change the visual design of the report for each user

C. To create new calculated columns automatically

D. To restrict data access by filtering rows based on the user's identity

Solution

1. Step 1: Understand RLS functionality

   RLS filters data rows so users only see data they are allowed to access.

2. Step 2: Compare options

   Speeding up refresh times, changing visual design, and creating calculated columns describe unrelated features; only restricting data access by filtering rows based on user identity matches RLS purpose.

3. Final Answer:

   To restrict data access by filtering rows based on the user's identity -> Option D

4. Quick Check:

   RLS filters rows by user identity = C [OK]

Hint: RLS controls data visibility per user, not report design [OK]

Common Mistakes:

• Confusing RLS with report formatting
• Thinking RLS speeds up data refresh
• Believing RLS creates new columns

2. Which of the following is the correct way to define a filter in a Power BI RLS role?

easy

A. FILTER('Sales', 'Sales'[Region] = "USERNAME()")

B. FILTER('Sales', 'Sales'[Region] == USERNAME())

C. FILTER('Sales', 'Sales'[Region] = USERPRINCIPALNAME())

D. FILTER('Sales', 'Sales'[Region] = USERNAME())

Solution

1. Step 1: Identify correct DAX function for user identity

   USERPRINCIPALNAME() returns the logged-in user's full username, commonly used in RLS filters.

2. Step 2: Check syntax correctness

   FILTER('Sales', 'Sales'[Region] = USERPRINCIPALNAME()) uses correct syntax and function. Using double equals (==), treating the function as a string, or using USERNAME() instead misuses syntax or less preferred function.

3. Final Answer:

   FILTER('Sales', 'Sales'[Region] = USERPRINCIPALNAME()) -> Option C

4. Quick Check:

   Use USERPRINCIPALNAME() for RLS filters = A [OK]

Hint: Use USERPRINCIPALNAME() for user-based filters in RLS [OK]

Common Mistakes:

• Using USERNAME() instead of USERPRINCIPALNAME()
• Using double equals (==) in DAX
• Putting USERNAME() inside quotes

3. Given this RLS filter on the 'Sales' table:
FILTER('Sales', 'Sales'[Salesperson] = USERPRINCIPALNAME())
If the logged-in user is '[e-mail]', what data will Alice see?

medium

A. All sales data regardless of salesperson

B. Only sales where 'Salesperson' equals '[e-mail]'

C. No data because USERPRINCIPALNAME() is invalid

D. Sales data for all salespersons except '[e-mail]'

Solution

1. Step 1: Understand the filter condition

   The filter keeps rows where 'Salesperson' matches the logged-in user's email.

2. Step 2: Apply the filter for user '[e-mail]'

   Only rows with 'Salesperson' = '[e-mail]' remain visible to Alice.

3. Final Answer:

   Only sales where 'Salesperson' equals '[e-mail]' -> Option B

4. Quick Check:

   RLS shows user-specific rows = B [OK]

Hint: RLS filters rows matching logged-in user exactly [OK]

Common Mistakes:

• Assuming all data is visible
• Thinking USERPRINCIPALNAME() is invalid
• Believing data for other users is shown

4. You created this RLS role filter:
FILTER('Orders', 'Orders'[Country] = USERNAME())
But users report seeing no data. What is the most likely issue?

medium

A. USERNAME() returns only the username, not the country name

B. FILTER function is not supported in RLS roles

C. The table name 'Orders' is misspelled

D. RLS roles cannot filter by text columns

Solution

1. Step 1: Understand USERNAME() output

   USERNAME() returns the user's login name, not a country name.

2. Step 2: Compare USERNAME() with 'Country' column

   Since 'Country' contains country names, the filter never matches, so no data shows.

3. Final Answer:

   USERNAME() returns only the username, not the country name -> Option A

4. Quick Check:

   Mismatch between USERNAME() and column values = A [OK]

Hint: Match USERPRINCIPALNAME() or correct user info to column values [OK]

Common Mistakes:

• Assuming USERNAME() returns country
• Thinking FILTER is unsupported in RLS
• Ignoring case sensitivity in filters

5. You want to protect sensitive sales data so that each regional manager sees only their region's data. Which approach best uses RLS to achieve this?

hard

A. Create a role with filter 'Sales'[Region] = LOOKUPVALUE('Managers'[Region], 'Managers'[Email], USERPRINCIPALNAME()) and assign managers by email

B. Do not use RLS; instead, create separate reports for each region

C. Create a role with filter 'Sales'[Region] = "Region1" and assign all managers to this role

D. Create a role with filter 'Sales'[Region] = USERPRINCIPALNAME() and assign managers their email as region

Solution

1. Step 1: Understand the need for dynamic filtering

   Each manager should see data for their assigned region, which may differ from their username.

2. Step 2: Use LOOKUPVALUE to map manager email to region

   Create a role with filter 'Sales'[Region] = LOOKUPVALUE('Managers'[Region], 'Managers'[Email], USERPRINCIPALNAME()) and assign managers by email uses LOOKUPVALUE to find the manager's region from a 'Managers' table, enabling correct filtering.

3. Step 3: Evaluate other options

   Create a role with filter 'Sales'[Region] = USERPRINCIPALNAME() and assign managers their email as region incorrectly assumes email equals region; C assigns all managers to one region; B avoids RLS and is less efficient.

4. Final Answer:

   Create a role with filter 'Sales'[Region] = LOOKUPVALUE('Managers'[Region], 'Managers'[Email], USERPRINCIPALNAME()) and assign managers by email -> Option A

5. Quick Check:

   Use LOOKUPVALUE for dynamic RLS filtering = D [OK]

Hint: Use LOOKUPVALUE to map users to their data dynamically [OK]

Common Mistakes:

• Assuming username equals region directly
• Assigning all users to one static region
• Avoiding RLS and duplicating reports