Testing RLS in Desktop in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Row-Level Security (RLS) Behavior in Power BI Desktop

When you define Row-Level Security (RLS) roles in Power BI Desktop and test them using the 'View as Roles' feature, which of the following statements is true?

A The data is filtered exactly as it would be for the selected role when published to the Power BI Service.

B The data is filtered only partially and may show more rows than the actual role would see in the service.

C The 'View as Roles' feature does not filter data; it only highlights rows related to the role.

D RLS roles cannot be tested in Power BI Desktop; testing is only possible after publishing.

💡 Hint

Think about how Power BI Desktop simulates the role's data view before publishing.

Power BI Desktop's 'View as Roles' feature simulates the exact data filtering that the selected RLS role would have when the report is published to the Power BI Service. This allows you to verify RLS behavior locally.

🎯 Scenario

intermediate

Testing Multiple RLS Roles in Power BI Desktop

You have created two RLS roles: 'SalesRegionEast' and 'SalesRegionWest'. You want to test the combined effect of these roles in Power BI Desktop. What happens when you select both roles simultaneously in the 'View as Roles' dialog?

A Data is filtered to show only rows visible to both roles simultaneously (intersection).

B Data is filtered to show rows visible to either 'SalesRegionEast' or 'SalesRegionWest' (union of roles).

C Only the first selected role is applied; the second is ignored.

D Power BI Desktop throws an error because multiple roles cannot be selected together.

💡 Hint

Consider how multiple roles combine their filters in Power BI Desktop.

When multiple roles are selected in 'View as Roles', Power BI Desktop applies the union of their filters, showing all data rows visible to any of the selected roles.

🔧 Formula Fix

advanced

Diagnosing RLS Filter Not Applying in Power BI Desktop

You created an RLS role with a DAX filter on the 'Region' column: [Region] = "East". However, when testing in Power BI Desktop using 'View as Roles', the data is not filtered and all regions are visible. What is the most likely cause?

A Power BI Desktop does not support filtering on text columns in RLS roles.

B The DAX filter syntax is incorrect and causes the filter to be ignored.

C The 'Region' column is not included in the data model's relationships, so the filter does not propagate.

D The 'View as Roles' feature only works after publishing to the Power BI Service.

💡 Hint

Think about how RLS filters propagate through relationships in the data model.

If the column used in the RLS filter is not properly related in the data model, the filter will not affect related tables, causing the data to appear unfiltered in 'View as Roles'.

❓ dax_lod_result

advanced

Evaluating RLS Impact on a DAX Measure in Power BI Desktop

Given a table 'Sales' with columns 'Region' and 'Amount', and an RLS role filtering [Region] = "West", what is the result of the following DAX measure when viewed as that role?

TotalSales = SUM(Sales[Amount])

Assume the data is:

• East: 1000
• West: 2000
• North: 1500

A 0

B 4500

C 1000

D 2000

💡 Hint

Remember that RLS filters the data before the measure calculation.

The RLS role filters the 'Sales' table to only include rows where Region = 'West'. The measure sums only those rows, resulting in 2000.

❓ visualization

expert

Designing a Power BI Desktop Report to Test RLS Effectively

You want to create a Power BI Desktop report page that clearly shows the effect of RLS roles on sales data by region. Which visualization setup best helps you verify RLS filtering when using 'View as Roles'?

A A matrix visual showing 'Region' rows and 'Total Sales' values, with no slicers or filters applied.

B A card visual showing overall total sales without any region breakdown or filters.

C A table visual showing 'Region' and 'Total Sales' with a slicer on 'Region' to manually filter data.

D A clustered bar chart showing 'Region' on the axis and 'Total Sales' as values, with a page-level filter on 'Region'.

💡 Hint

Think about a visualization that automatically reflects RLS filtering without manual user filtering.

A matrix visual listing all regions with their total sales will show only the regions visible to the RLS role when using 'View as Roles'. This makes it easy to verify RLS filtering effects without manual slicers or filters.

Practice

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