Masking data based on user's permission group

[Klaudia_00]

Hi Community,

Is there a way to mask some column values based on who is connecting to the model? 

Let's take as an example such table:

I have different group permission: PBI-Admin and PBI-NonUS set up in PoweBI service. What I want to achive is:

1. if user is member of PBI-Admin group, he/she can see everything without changes,

2. if user is a member of PBI-NonUS group and the case is from USA, then I want to mask "Text Field" value (display "MASKED").

It's very essential, I only want to cover a single cell. All users should see the case number regardless of country.

The case involves my large PBI model, from which many reports have been created. Moreover, the model is published and users can create their own analysis.

The issue does not apply to RLS (the whole row disappears) or OLS (the whole column disappears).

I will also need to implement this in the reports created in report builder (source: Azure SQL db).

I look forward to your ideas or experiences!

[FarhanJeelani · Accepted solution]

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:

UserName	Group
user1@domain.com	PBI-Admin
user2@domain.com	PBI-NonUS

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.

[v-hashadapu]

Hi @Klaudia_00 , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @Klaudia_00 , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @Klaudia_00 , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu · Accepted solution]

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.

[Klaudia_00]

Thanks for the reply, I will test it on my end focusing firstly on PBI desktop. Unfortunately, I'm afraid it will affect performance a lot.

I was wondering if there is another way, such as shared expressions in tabular editor and implementation of this in M language (where you connect to a specific view on the database)?

[FarhanJeelani · Accepted solution]

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:

UserName	Group
user1@domain.com	PBI-Admin
user2@domain.com	PBI-NonUS

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.