Dynamic RLS with USERNAME in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

DAX Measure Output with USERNAME() in Dynamic RLS

Consider a table Sales with columns SalesAmount and UserEmail. A dynamic RLS filter uses USERNAME() to filter rows where UserEmail matches the logged-in user.

Given this DAX measure:

Filtered Sales = CALCULATE(SUM(Sales[SalesAmount]), Sales[UserEmail] = USERNAME())

If the logged-in user is [e-mail] and the Sales table has these rows:

• (100, "[e-mail]")
• (200, "[e-mail]")
• (150, "[e-mail]")

What is the value of Filtered Sales?

Power BI

Filtered Sales = CALCULATE(SUM(Sales[SalesAmount]), Sales[UserEmail] = USERNAME())

A 250

B 450

C 200

D 0

💡 Hint

Think about which rows match the USERNAME() filter.

The measure sums SalesAmount only for rows where UserEmail equals the logged-in user [e-mail]. Those rows have amounts 100 and 150, totaling 250.

❓ visualization

intermediate

Best Visualization for Dynamic RLS User Sales Summary

You want to create a Power BI report page that shows each logged-in user only their own sales summary using dynamic RLS with USERNAME(). Which visualization type is best to clearly show the total sales amount for the current user?

A Stacked bar chart showing sales by all users

B Scatter plot of sales amount vs. user email

C Card visualization showing total sales amount

D Table showing sales for all users

💡 Hint

Think about a simple, clear way to show a single number filtered by RLS.

A Card visualization is best for showing a single summary number like total sales for the logged-in user. Other options show multiple users or complex visuals that are not filtered clearly by dynamic RLS.

❓ data_modeling

advanced

Designing a Dynamic RLS Model with USERNAME() and Role Table

You have a Users table with columns UserEmail and Region. Your Sales table has a Region column. You want to apply dynamic RLS so each user sees only sales from their region.

Which DAX filter expression correctly implements this dynamic RLS in the role definition?

A Sales[Region] = LOOKUPVALUE(Users[UserEmail], Users[Region], USERNAME())

B FILTER(Sales, Sales[Region] = LOOKUPVALUE(Users[Region], Users[UserEmail], USERNAME()))

C Sales[Region] = USERNAME()

D FILTER(Users, Users[UserEmail] = Sales[Region])

💡 Hint

Think about matching the sales region to the user's region found by USERNAME().

Option B filters sales rows where the region matches the region of the logged-in user found by looking up Users[Region] using USERNAME(). Other options mismatch columns or logic.

🔧 Formula Fix

advanced

Identify the Error in Dynamic RLS Using USERNAME()

Given this RLS filter expression in Power BI:

Sales[UserEmail] = USERNAME()

Users report that after publishing, no data is visible for any user. What is the most likely cause?

A USERNAME() returns the user's email but the filter should use USERPRINCIPALNAME()

B The filter syntax is invalid and causes a syntax error

C Sales[UserEmail] column is missing from the model

D USERNAME() returns domain\username but UserEmail stores email addresses, causing no matches

💡 Hint

Consider the format of USERNAME() output versus stored emails.

USERNAME() often returns the domain and username (e.g., domain\user), but the UserEmail column stores full email addresses (e.g., [e-mail]). This mismatch causes the filter to exclude all rows.

🧠 Conceptual

expert

Scenario: Combining Dynamic RLS with Hierarchical Roles

Your company has a hierarchy of users: regional managers and sales reps. Sales reps should see only their own sales. Regional managers should see sales for all reps in their region.

You have a Users table with UserEmail, Region, and Role columns. Sales have a SalesRepEmail and Region.

Which approach best implements dynamic RLS to meet these requirements?

A Create two roles: one filters sales where SalesRepEmail = USERNAME(); the other filters sales where Region = LOOKUPVALUE(Users[Region], Users[UserEmail], USERNAME())

B Create one role filtering sales where SalesRepEmail = USERNAME() OR Region = USERNAME()

C Create a role filtering sales where SalesRepEmail = LOOKUPVALUE(Users[UserEmail], Users[Role], "SalesRep")

D Create roles with no filters and rely on report page filters to limit data

💡 Hint

Think about how to separate access by role and region using dynamic filters.

Option A correctly creates two roles: one for sales reps filtered by their own email, and one for regional managers filtered by region. This respects hierarchy and dynamic RLS principles. Other options mix filters incorrectly or rely on manual filtering.

Practice

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

easy

A. To change the report theme dynamically

B. To filter data based on the logged-in user's identity

C. To calculate the total sales amount

D. To create new columns in the data model

Solution

1. Step 1: Understand USERNAME() function

   The USERNAME() function returns the current logged-in user's email or username.

2. Step 2: Apply USERNAME() in Dynamic RLS

   Dynamic RLS uses USERNAME() to filter data so each user sees only their allowed rows.

3. Final Answer:

   To filter data based on the logged-in user's identity -> Option B

4. Quick Check:

   USERNAME() filters data by user [OK]

Hint: USERNAME() filters data by current user identity [OK]

Common Mistakes:

• Thinking USERNAME() creates new columns
• Using USERNAME() to calculate totals
• Confusing USERNAME() with theme settings

2. Which of the following is the correct syntax to create a filter rule for Dynamic RLS using USERNAME() in Power BI?

easy

A. [UserEmail] = USERNAME()

B. FILTER([UserEmail], USERNAME())

C. USERNAME() = [UserEmail]

D. CALCULATE(USERNAME(), [UserEmail])

Solution

1. Step 1: Understand filter rule syntax

   In Dynamic RLS, the filter rule compares a column to the current user using =.

2. Step 2: Identify correct syntax

   The correct syntax is [UserEmail] = USERNAME() to filter rows matching the logged-in user.

3. Final Answer:

   [UserEmail] = USERNAME() -> Option A

4. Quick Check:

   Filter rule uses column = USERNAME() [OK]

Hint: Use column = USERNAME() for filter rules [OK]

Common Mistakes:

• Using USERNAME() on left side of comparison
• Using FILTER() function incorrectly
• Trying to CALCULATE USERNAME()

3. Given a table with a column UserEmail and a Dynamic RLS filter rule [UserEmail] = USERNAME(), what data will a user with email [e-mail] see?

medium

A. Only rows where UserEmail is not [e-mail]

B. All rows regardless of UserEmail

C. Only rows where UserEmail is [e-mail]

D. No rows at all

Solution

1. Step 1: Understand the filter rule effect

   The rule [UserEmail] = USERNAME() filters rows to only those matching the logged-in user's email.

2. Step 2: Apply to user email

   For user [e-mail], only rows with UserEmail = [e-mail] are visible.

3. Final Answer:

   Only rows where UserEmail is [e-mail] -> Option C

4. Quick Check:

   Filter shows rows matching USERNAME() [OK]

Hint: User sees only rows matching their email [OK]

Common Mistakes:

• Assuming user sees all rows
• Thinking user sees rows with different emails
• Believing no rows are shown

4. You wrote this RLS filter rule: FILTER(Users, Users[Email] = USERNAME()). Why does it cause an error in Power BI?

medium

A. FILTER cannot be used directly as a filter rule; it expects a boolean expression

B. USERNAME() is not allowed in RLS rules

C. The column name should be lowercase

D. FILTER requires two arguments

Solution

1. Step 1: Understand RLS filter rule requirements

   RLS filter rules expect a boolean expression, not a table expression like FILTER().

2. Step 2: Identify the error cause

   Using FILTER() returns a table, causing a syntax error in RLS rules.

3. Final Answer:

   FILTER cannot be used directly as a filter rule; it expects a boolean expression -> Option A

4. Quick Check:

   RLS rules need boolean expressions [OK]

Hint: Use boolean expressions, not FILTER(), in RLS rules [OK]

Common Mistakes:

• Using FILTER() instead of boolean condition
• Thinking USERNAME() is disallowed
• Assuming column case matters
• Believing FILTER needs two arguments

5. You want to create Dynamic RLS so that managers see data for their team members, and team members see only their own data. The table has columns UserEmail and ManagerEmail. Which DAX filter rule correctly implements this?

hard

A. USERNAME() = [UserEmail] && USERNAME() = [ManagerEmail]

B. [UserEmail] = USERNAME() && [ManagerEmail] = USERNAME()

C. FILTER(Users, [UserEmail] = USERNAME() || [ManagerEmail] = USERNAME())

D. [UserEmail] = USERNAME() || [ManagerEmail] = USERNAME()

Solution

1. Step 1: Understand the requirement

   Managers should see rows where they are the manager, and users see their own rows.

2. Step 2: Choose correct logical operator

   Use OR (||) to allow either condition: user is the row owner or user is the manager.

3. Step 3: Validate syntax

   [UserEmail] = USERNAME() || [ManagerEmail] = USERNAME() uses correct syntax and logic: [UserEmail] = USERNAME() || [ManagerEmail] = USERNAME().

4. Final Answer:

   [UserEmail] = USERNAME() || [ManagerEmail] = USERNAME() -> Option D

5. Quick Check:

   Use OR (||) to combine user and manager filters [OK]

Hint: Use OR (||) to combine user and manager filters [OK]

Common Mistakes:

• Using AND (&&) instead of OR (||)
• Reversing USERNAME() and column order
• Using FILTER() in RLS rule