Dynamic RLS with USERNAME in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the DAX expression to get the current user's username.

Power BI

CurrentUser = [1]()

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

AUSEREMAIL

BUSERPRINCIPALNAME

CUSERNAME

DUSERID

Attempts:

3 left

💡 Hint

Common Mistakes

Using USERPRINCIPALNAME() instead of USERNAME()

Trying to use USEREMAIL() which is not a valid DAX function

Using USERID() which does not exist in DAX

The USERNAME() function returns the current user's username in Power BI, which is essential for dynamic row-level security.

2fill in blank

medium

Complete the FILTER expression to restrict data to rows where the 'Salesperson' column matches the current user.

Power BI

FILTER(Sales, Sales[Salesperson] = [1]())

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

AUSERNAME

BCURRENTUSER

CUSERPRINCIPALNAME

DUSEREMAIL

Attempts:

3 left

💡 Hint

Common Mistakes

Using USERPRINCIPALNAME() which returns email format and may not match 'Salesperson' values

Using a non-existent function CURRENTUSER()

Using USEREMAIL() which is invalid in DAX

The USERNAME() function returns the current user's username, which you compare to the 'Salesperson' column to filter rows dynamically.

3fill in blank

hard

Fix the error in the DAX expression to correctly filter rows for dynamic RLS.

Power BI

FILTER(Employees, Employees[Username] = [1])

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

AUSERNAME()

BUSERNAME

CUSERPRINCIPALNAME()

DCURRENTUSER()

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting parentheses after USERNAME

Using USERPRINCIPALNAME() which might not match the 'Username' column format

Using a non-existent function CURRENTUSER()

The function USERNAME() must include parentheses to be called correctly in DAX.

4fill in blank

hard

Fill both blanks to create a DAX filter that allows access only if the current user matches the 'UserEmail' column.

Power BI

FILTER(Users, Users[UserEmail] = [1]() && [2](Users[UserEmail], 12) = "@company.com")

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

AUSERPRINCIPALNAME

BUSERNAME

CRIGHT

DLEFT

Attempts:

3 left

💡 Hint

Common Mistakes

Using USERNAME() which returns username, not email

Using LEFT instead of RIGHT to check email domain

Omitting parentheses after USERPRINCIPALNAME

USERPRINCIPALNAME() returns the user's email, and RIGHT extracts the domain part to check if it matches '@company.com'.

5fill in blank

hard

Fill all three blanks to create a DAX expression that filters the 'Sales' table for rows where the 'Region' matches the user's region stored in a 'UserRegion' table.

Power BI

FILTER(Sales, Sales[Region] = RELATED([1][[2]]) && RELATED([1][Email]) = [3]())

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

AUserRegion

BRegionName

CUSERNAME

DUSERPRINCIPALNAME

Attempts:

3 left

💡 Hint

Common Mistakes

Using USERNAME instead of USERPRINCIPALNAME for email comparison

Swapping table and column names

Omitting parentheses after USERPRINCIPALNAME

RELATED(UserRegion[RegionName]) gets the user's region, and USERPRINCIPALNAME() returns the user's email to match the current user.

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