Bidirectional Cross-Filtering and Row-Level Security

Angelica Choo Quan, a trainer at Pragmatic Works, explains how to implement bidirectional cross-filtering in Power BI reports and how it interacts with row-level security (RLS). Using Adventure Works data as an example, she provides a step-by-step guide for report builders to restrict user access to specific data.

Connecting to Data and Initial Setup

Angelica starts by connecting to the Adventure Works dataset. The report contains multiple visuals:

• Sales Amount by Country – displays sales for each country.
• Sales Amount by Model Name – breaks down sales by product model.
• Customer Table – includes customer names, emails, phone numbers, and account details.

For demonstration, Angelica focuses on restricting user access to certain countries using roles in Power BI Desktop, while assigning users to these roles in the Power BI Service.

Creating Roles for Row-Level Security

She highlights the process of creating RLS roles:

1. Navigate to Modeling > Manage Roles in Power BI Desktop.
2. Create roles to restrict users to specific countries (e.g., US Role, France Role).
3. Apply filters on the relevant tables (e.g., Sales Territory table) to match the country condition.
4. Save and close the role setup, ensuring the changes are applied.

After creating roles, she demonstrates how to test them using the View as Role feature. Initially, the US Role filters the sales data correctly but does not automatically restrict customer information in related tables.

Understanding Single vs. Bidirectional Relationships

Angelica explains that by default, relationships in Power BI are single-directional:

• Filters propagate from the "one" side to the "many" side of a relationship.
• Single-direction filtering may not apply security restrictions to related tables, such as the Customer table.

To fully enforce RLS across related tables, the relationship must be updated to bidirectional cross-filtering.

Modifying Relationships for Bidirectional Filtering

To enable bidirectional filtering:

1. Open the Model View in Power BI Desktop.
2. Select the relationship between tables (e.g., Customer table and Internet Sales table).
3. Change the Cross Filter Direction from single to both.
4. Toggle the option to apply security in both directions.
5. Click Apply Changes.

After applying these changes, the customer table now reflects restricted access according to the defined roles. For instance, the US Role only displays customers from the United States, while the France Role displays customers from France. This ensures that RLS works across all relevant visuals and tables.

Testing and Verification

Angelica demonstrates testing roles in the report view:

• Switching to the US Role filters both sales and customer data correctly.
• Switching to the France Role shows different data according to the role's restrictions.
• Returning to a default view restores access to all customer and sales data.

By applying bidirectional cross-filtering, report builders gain greater control over RLS implementation, ensuring that sensitive data is visible only to authorized users.

Key Takeaways

• Single-direction filters do not propagate security restrictions to all related tables.
• Bidirectional cross-filtering allows RLS to apply across multiple tables effectively.
• Always test roles using the View as Role feature to verify security restrictions.
• Careful relationship management ensures accurate and secure reporting for all users.

Angelica concludes by encouraging viewers to check her previous video on basic row-level security for more foundational knowledge. She also invites viewers to explore Pragmatic Works’ on-demand learning platform for courses on Azure, Power BI, Power Apps, Power Automate, and more.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power BI and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.