Combined Row Level Security and Column Level Security in PowerBI

Two ways of implementing RLS and CLS both combined in PowerBI
Method 1 : By Introducing a new table and combination of DAX. Output: The column/field will be masked and visible in the visual based on user persona with RLS
Method 2: By utilizing fieldparameters and combination of DAX. Output: The column/field will be hidden/not included in the visual based on user persona with RLS
Method 2 is my preferred approach, comment down below, which one you would prefer :)
Use case: Employee and Supervisor, 2 user personas
User Persona 1 : Employee: Scenario 1: Visual with Data Type Text in a Table Visual for CLS:
- When an employee accesses the PBI report, RLS should be applied to the report and employees must see only their respective records. Also, CLS should be applied and the “STI Eligible field” should be masked.
Scenario 2: Visual with Data Type Numeric in a Bar Graph for CLS:
- When an employee accesses the PBI report, RLS should be applied to the report and employees must see only their respective records. Also, CLS should be applied and the “TM_Count” measure should be masked.
User Persona 2: Supervisor: Scenario 1 & 2: For both data types of Text and Numeric, - The Supervisor should see unmasked information and their respective assigned Employees information in the report. - Also, signed in Supervisor should see their respective information in the report.
Method 1 : Authorized Users Table and DAX
Pre-requisites CLS : For Data Type Text — Secnario 1
Data Model Overview:

Create a new table named Authorized User:
- This table contains a list of users who should see the PII columns un masked
For Example:

Create a dummy masked column: A new dummy column must be created in the table that contains a PII column either from source or via DAX in PowerBI. [PII: Personally identifiable information — Column with Sensitive Data, required to be Masked]
For Example, dummy column created in PowerBI: STL Eligible column here is a PII column

A new dummy calculated column within PowerBI is created as below

DAX used to create this column:
STI (MASKED) = REPT(“*”, 7)
Create a New Measure: The below measure helps to view PII unmasked column based on a condition if the signed in user’s info is existing in the Authorized User Table via DAX function IF, Contains & UserPrincipalName()
If the signed in user’s info is not existing in the Authorized User Table, then it will return the dummy masked column as output
STI (PII) =
VAR showVal = IF(
CONTAINS(‘Authorized Users’,’Authorized Users’[User],USERPRINCIPALNAME()),
1,0)
RETURN IF(showVal=1,
VALUES(‘TM Org Info’[STI Eligible]),
VALUES(‘TM Org Info’[STI (MASKED)]
))
Pre-requisites CLS : For Data Type Numeric — Measure— Secnario 2
Authorized User Table needs to be created [As explained above]
Create a New Measure: The below measure helps to view PII unmasked measure based on a condition if the signed in user’s info is existing in the Authorized User Table via DAX function IF, Contains & UserPrincipalName().
If the signed in user’s info is not existing in the Authorized User Table, then it will return -1 as output
TM_COUNT (PII) = IF(
CONTAINS(‘Authorized Users’, ‘Authorized Users’[User], USERPRINCIPALNAME()),
COUNT(DYN_TM_FULL_COMPLETIONS[Team Member ID]),
-1)
Pre-requisites RLS
Create Roles in PowerBI based on use case


Outputs:
Combination of CLS and RLS: Data Type Text in a Table Visual
When an Employee access the report

RLS is applied and only Employee Records are displayed. CLS is applied and STI (PII) column is masked
When a Supervisor access the report

RLS is applied and Supervisor + Employee records are displayed. CLS is not applied and STI (PII) column is unmasked masked
Combination of CLS and RLS: Data Type numeric in a Bar Graph
When an Employee accesses the report

RLS is applied and only Employee records are displayed. CLS is applied and TM_Count(PII) measure is masked to -1
When a supervisor accesses the report

RLS is applied and Supervisor + Employee records are displayed. CLS is not applied and TM_Count(PII) measure is unmasked masked.
Pro’s of this approach
- Easy to Implement
- User Access Control is within PowerBI to show Masked and Unmasked information with the help of DAX and Authorized Users Table
Con’s of this approach
- Works for one audience group with one Authorized Table For example: - If there is a use case to showcase 2 Audience Groups, 2 masked columns differently, then we need to create 2 different Authorized Users Tables for 2 different scenarios
- Maintenance of Authorized table is an additional activity
- Need to create multiple measure for multiple PII columns
To overcome these Con’s we can look at Method 2
Method 2: CLS + RLS via Fields Parameters & DAX
Pre-requisites CLS : For Data Type Text — Secnario 1
Create a new Fields Parameter
Parameter text PII = {
("STI Eligible", NAMEOF('TM Org Info'[STI Eligible]), 0),
("Supervisor Email Address", NAMEOF('TM Org Info'[Supervisor Email Address]), 1)
}
The above DAX generates a New Field Parameter which contains STI Eligible and Supervisor Email Address.

Use the created Parameter filed in the required visuals where masking is required

Create 2 new Managed Roles For Example:


The Managed Roles + Parameters combined together helps with showcasing and hiding the fields in the visual, which acts as CLS
Pre-requisites CLS : For Data Type Numeric — Measure— Secnario 2
Create a new Fields Parameter
Parameter numeric PII = {
("TM_COUNT (PII)", NAMEOF('All Measures'[TM_COUNT (PII)]), 0)
}
The above DAX generates a New Field Parameter which contains TM_Count (PII) Measure.

Use the created Parameter filed in the required visuals where masking is required

Create a new Managed Role


Pre-requisites RLS
Add additional conditions to the existing Managed Roles For Example: Visual with Data Type Text:


Visual with Data Type Numeric [Measure]:



Add respective user Persona AAD groups for the dataset in the security setting within PowerBI Service
Outputs:
Combination of CLS and RLS: Data Type Text in a Table Visual
When an Employee access the report

RLS is applied and only Employee Records are displayed. CLS is applied and STI (PII) column is not included in the visual hence not visible
When a Supervisor access the report

RLS is applied and Supervisor + Employee records are displayed. CLS is not applied and STI (PII) column is included and visible
Combination of CLS and RLS: Data Type numeric in a Bar Graph
When an Employee accesses the report

RLS is applied and only Employee records are displayed. CLS is applied and TM_Count(PII) measure is not included in the visual hence not visible
When a supervisor accesses the report

RLS is applied and Supervisor + Employee records are displayed. CLS is not applied and TM_Count(PII) measure is included and visible
Pro’s of this approach
- Overcomes the Con of CLS+RLS via Authorized User Table and DAX method, satisfies multiple columns masking with one field Parameter and Multiple Managed Roles
- No need to create and maintain Authorised User table
- Easy to Add/Remove users from AAD groups
- CLS Field/Measure is not shown to end user at all. Adds additional layer of security and control
Con’s of this approach
- Create and maintain multiple AAD Groups
- * Note: Fields/Metrics used for CLS is not recommended to be used in Slicer.