✅ How to Set Up Row-Level Security for Billing Data in Azure Synapse or Power BI

To ensure users only see billing data relevant to their team or department, you can implement Row-Level Security (RLS). Power BI and Azure Synapse support RLS, though each platform handles it differently.

🔒 Option 1: Power BI Row-Level Security

Power BI RLS filters data based on the viewer’s identity, ideal for shared cost dashboards.

🧩 A. Define Roles in Power BI Desktop

Open your report in Power BI Desktop.
Go to the Modeling tab > click Manage Roles.
Create a new role (e.g., DeptA Access).
Apply a DAX filter: DAXCopyEdit[DepartmentTag] = "DeptA"

⚙️ B. Use Dynamic RLS (Recommended)

Add a User Mapping table:
- Columns: UserEmail, Department
In Manage Roles, use this DAX formula: DAXCopyEditCostTable[DepartmentTag] = LOOKUPVALUE(UserMap[Department], UserMap[UserEmail], USERPRINCIPALNAME())
This allows one dynamic role for all users.

🚀 C. Publish & Assign Roles

Publish your report to the Power BI Service.
Go to the dataset > click Security.
Assign users or AAD groups to roles you defined.
Test using “View as Role” in Desktop or via assigned users.

✅ Result: Users only see data filtered by their role or email-based mapping.

🧱 Option 2: Azure Synapse or SQL Row-Level Security

Use SQL-based RLS when you’re working with Synapse SQL pools or Azure SQL databases, especially in DirectQuery scenarios.

🔐 A. Set Up a User Mapping Table

Create a table linking users to allowed filters:

sqlCopyEditCREATE TABLE UserAccess (
  UserName NVARCHAR(256),
  DepartmentTag NVARCHAR(100)
);

🔧 B. Create a Security Predicate Function

sqlCopyEditCREATE FUNCTION dbo.fn_FilterCostByUser()
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS AccessAllowed
WHERE EXISTS (
  SELECT 1 FROM dbo.UserAccess u
  WHERE u.UserName = SESSION_USER 
    AND u.DepartmentTag = CostTable.DepartmentTag
);

📜 C. Apply an RLS Security Policy

sqlCopyEditCREATE SECURITY POLICY CostRLS
ADD FILTER PREDICATE dbo.fn_FilterCostByUser() ON dbo.CostTable,
WITH (STATE = ON);

✅ Result: SQL will automatically filter rows based on the executing user’s identity.

⚠️ Important Notes

Scenario	Use This
Power BI Import model	Use Power BI RLS – data loads once, filters apply per viewer
Power BI DirectQuery	Either Power BI RLS or SQL RLS
Synapse Studio or external tools	Use SQL RLS for secure access control
Spark / Data Lake	Use file ACLs instead of row-level filters

👥 Best Practice: Use Azure AD Groups

Assign AAD groups (e.g., Finance-RLS) to RLS roles instead of individual users.
Maintain access centrally in Azure AD instead of updating Power BI or SQL each time someone joins/leaves.

✅ Summary

Platform	RLS Method	How It Works
Power BI	Roles + DAX Filters	Filters rows in reports based on viewer identity
Synapse / SQL	SQL Security Policy	Applies filters at the database level per executing user

For most Azure cost reporting scenarios, Power BI RLS is easiest and most flexible, especially when working with tagged data or subscription filters.

Knowledge Base