Fixing Power BI RLS that Returns only the first matching row

by

Summary

A production report implemented Row-Level Security (RLS) using a central access mapping table. The logic was designed to allow users to access specific identifiers (IC) or gain full access via an “ALL” wildcard. However, the implementation failed by only returning the first matching row for any given user, effectively breaking data visibility for users with multiple permissions.

Root Cause

The failure stems from a fundamental misunderstanding of how DAX evaluates Boolean expressions within an RLS filter context.

  • Contextual Evaluation: RLS filters are applied row-by-row to the table being secured (ExlSeparations).
  • The IN Operator Limitation: While the IN operator is syntactically correct, the way the AllowedICs variable was constructed and evaluated within the RLS engine often defaults to a single-row evaluation context if the filter expression is not explicitly handled as a set-based comparison against the current row.
  • The Variable Scope: The variable AllowedICs creates a virtual table. When the engine evaluates ExlSeparations[IC Code] IN AllowedICs, it expects a clear relationship between the current row’s value and the entire list. If the engine interprets the logic as a scalar comparison rather than a set membership test due to the complexity of the FILTER function used, it may stop at the first match found in the lookup table.
  • Data Integrity Issues: The underlying cause of the “need” for this complex RLS was low-quality source data (manual Excel entries like “TIIC” vs “TI-IC”), which forced the engineer to build a “workaround” security model rather than a normalized data model.

Why This Happens in Real Systems

This issue is a classic symptom of Complex Security Logic vs. Data Normalization.

  • Manual Data Entry: When business users manage Excel files, they introduce entropy (variations in strings).
  • Disconnected Models: To avoid relationship issues with messy data, engineers often build “unrelated” models. This breaks the natural propagation of filters that Power BI is designed to use.
  • Over-Engineering Security: Instead of cleaning the data (the “Source of Truth”), the engineer tried to solve the problem at the Presentation Layer using complex DAX, which is harder to debug and more prone to performance bottlenecks.

Real-World Impact

  • Security Breach or Data Blindness: In the worst case, a user might see only a subset of the data they are legally authorized to view, leading to incorrect business decisions.
  • Operational Friction: High volumes of support tickets from users claiming “the report is broken” because they can only see one department’s data.
  • Performance Degradation: Using FILTER and SELECTCOLUMNS inside an RLS expression forces the engine to perform a nested loop join for every single row in the fact table, which scales poorly as data grows.

Example or Code (if necessary and relevant)

The original faulty logic:

VAR CurrentUser = LOWER(USERPRINCIPALNAME())
VAR AllowedICs = SELECTCOLUMNS(
    FILTER(
        OperationsAccess,
        LOWER(OperationsAccess[Email]) = CurrentUser
    ),
    "IC", OperationsAccess[IC]
)
RETURN
IF(
    "ALL" IN AllowedICs,
    TRUE(),
    ExlSeparations[IC Code] IN AllowedICs
)

The optimized, production-grade fix:

VAR CurrentUser = LOWER(USERPRINCIPALNAME())
VAR IsAdmin = 
    CONTAINS(
        'OperationsAccess', 
        'OperationsAccess'[Email], CurrentUser, 
        'OperationsAccess'[IC], "ALL"
    )
VAR UserAllowedICs = 
    CALCULATETABLE(
        VALUES('OperationsAccess'[IC]),
        'OperationsAccess'[Email] = CurrentUser
    )
RETURN
    IsAdmin || 'ExlSeparations'[IC Code] IN UserAllowedICs

How Senior Engineers Fix It

A senior engineer solves this by addressing the Data Architecture before writing the DAX.

  • Data Normalization: Implement a Mapping/Cleaning Layer in Power Query or SQL. Transform “TI-IC”, “TI IC”, and “TIIC” into a standardized “TIIC” before it ever reaches the model.
  • Star Schema Implementation: Instead of “no relationships,” create a proper Dimension Table for IC_Codes. Link all 20 sources to this single dimension.
  • Standardized RLS: Use the Relationship-based RLS pattern. Instead of complex IF logic, filter the OperationsAccess table, and let the bi-directional cross-filter (or a properly configured one-way filter) propagate the security to the fact tables.
  • Use CALCULATETABLE over FILTER: For RLS, CALCULATETABLE is more performant as it leverages the storage engine more effectively than the row-by-row FILTER function.

Why Juniors Miss It

  • Symptom vs. Disease: Juniors try to fix the symptom (the RLS not showing rows) by tweaking the DAX, whereas seniors fix the disease (the messy, unnormalized data).
  • Lack of Model Thinking: Juniors often treat Power BI like Excel—a collection of independent tables. They fail to understand that the power of a BI tool lies in the relationships and the filter context that flows through them.
  • Complexity Bias: Juniors often believe that a more complex DAX formula is “smarter,” while seniors know that the simplest code is the most robust code.

Leave a Comment