Masking data based on user's permission group

Dear Klaudia_00,

You're absolutely right — RLS hides entire rows, and OLS  hides entire columns, which doesn't help when you need cell-level masking (i.e., only mask specific values in a column based on conditions).

Here's how to achieve cell-level data masking in Power BI based on user groups like PBI-Admin and PBI-NonUS:

Goal:

• Everyone sees all rows.
• Only the value in the Text Field should be masked to "MASKED" if the user is in PBI-NonUS and the case is from USA.
• Admins (PBI-Admin) see everything.
• This must work in both Power BI and Paginated Reports (Report Builder) connected to Azure SQL DB.

Solution Overview (Power BI)
1. Create a Security Table
Create a table (can be manually created in Power BI or from Azure SQL) that maps usernames to groups:

2. Create a User-Based Masking Logic Measure/Column
Assuming your table is named Cases and you have UsernameGroupMapping with a relationship on the current user:

A. Create a DAX measure for the masked text field:

Masked Text Field =
VAR CurrentUser = USERPRINCIPALNAME()
VAR UserGroup =
CALCULATE(
MAX('UsernameGroupMapping'[Group]),
'UsernameGroupMapping'[UserName] = CurrentUser
)
RETURN
IF(
UserGroup = "PBI-NonUS" && Cases[Country] = "USA",
"MASKED",
Cases[Text Field]
)

Use this measure in your visual instead of the original Text Field.

3. (Optional) Create a Calculated Column for Use in Paginated Reports
Since Paginated Reports may need queryable data (not just measures), create a calculated column with similar logic (but note: this exposes logic in the model):

MaskedText =
VAR CurrentUser = USERPRINCIPALNAME()
VAR UserGroup =
LOOKUPVALUE(
'UsernameGroupMapping'[Group],
'UsernameGroupMapping'[UserName], CurrentUser
)
RETURN
IF(
UserGroup = "PBI-NonUS" && Cases[Country] = "USA",
"MASKED",
Cases[Text Field]
)

For Paginated Reports from Azure SQL, use a similar CASE logic inside a SQL view or function, driven by the user login via SUSER_SNAME() or Power BI passed parameter.

For Azure SQL DB (Paginated Reports)
Since Power BI passes user context differently, you'd need to:

• Create a SQL View or Function that accepts username as a parameter.
• Implement masking logic like:

SELECT
CaseID,
Country,
CASE
WHEN @UserGroup = 'PBI-NonUS' AND Country = 'USA' THEN 'MASKED'
ELSE [Text Field]
END AS [Text Field]
FROM Cases

• In Report Builder, pass the current user name and group as parameters.

Please mark this post as solution if it helps you. Appreciate Kudos.

Hi Klaudia_00 , Thank you for reaching out to the Microsoft Community Forum.

Please try below:

1. I recommend creating a parameterized view in your Azure SQL database to handle masking efficiently at the source, minimizing Power BI’s workload. First, set up a UsernameGroupMapping table in SQL. To pass the user context, configure Power BI to use DirectQuery mode and pass USERPRINCIPALNAME() as a parameter to set SESSION_CONTEXT. Try below SQL to create vw_Cases_Masked:

CREATE VIEW vw_Cases_Masked

AS

SELECT

    CaseID,

    Country,

    CASE

        WHEN Country = 'USA' AND EXISTS (

            SELECT 1 FROM UsernameGroupMapping

            WHERE [Group] = 'PBI-NonUS' AND Username = SESSION_CONTEXT(N'UserName')

        )

        THEN 'MASKED'

        ELSE [Text Field]

    END AS [Text Field]

FROM Cases;

2. For dynamic masking in Power BI, use a DAX measure, Replace the raw Text Field with this measure in your reports. It leverages USERPRINCIPALNAME() to enforce group-based rules, ensuring PBI-Admin sees all values and PBI-NonUS sees "MASKED" for USA cases. This method is lightweight and supports your published model and user analyses. Example DAX for measure:
   Masked Text Field =

VAR CurrentUser = USERPRINCIPALNAME()

VAR UserGroup =

    CALCULATE(

        MAX('UsernameGroupMapping'[Group]),

        'UsernameGroupMapping'[Username] = CurrentUser

    )

RETURN

    IF(

        UserGroup = "PBI-NonUS" && Cases[Country] = "USA",

        "MASKED",

        Cases[Text Field]

    )

3. Connect Power BI to the vw_Cases_Masked view using M code. This avoids redundant logic in Power BI, relying on the SQL view’s masking. Since M can’t handle user context, this is the best use case for simply fetching pre-masked data. Test this connection in Desktop to ensure seamless integration. Example:
   let

    Source = Sql.Database("your_server_name", "your_database_name"),

    Cases = Source{[Schema="dbo",Item="vw_Cases_Masked"]}[Data]

in

    Cases

4. Use Tabular Editor to define the Masked Text Field measure as a shared expression for reusability across your model. Export the DAX measure above as a template and deploy it to your shared dataset. This avoids calculated columns that could bloat your model.
5. For Report Builder, connect to the vw_Cases_Masked view, which handles masking via SQL. If user context passing isn’t supported, add a parameter in Report Builder linked to a dataset querying UsernameGroupMapping to dynamically apply the masking logic.

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.