Dynamic Row Level Security for masking table column values

[pmor]

Hello Community,

I have a PBI model which has a fact table with a column SiteID which is used to joined to a dimension table for further detail of the sites. Some sites are public and some are private types. For External users I would like encrypt or mask all the private SiteIDs, SiteName and selected columns in the dimension table as PRIV but to have it all visible for Internal users.

I can mask data in measures but this is not what I want.

I want to still have the data/measures values displayed.

The tool provided to all users is a semantic model in an Excel PivotTable.

It would be great to have this because at the moment we are maintaining 2 models. And it may be that we need to have a SiteIDmasked column in the fact table which is a duplicate of SiteID but with PRIV for the private sites. And enact OLS. But this is annoying as admin users can see all columns and they are data model users too.

Hopefully someone has a solution. Thanks.

[v-sshirivolu]

Hi @pmor ,

To implement dynamic column masking in Power BI / Fabric, create masked columns in DimSite like SiteID_Display = IF([SiteType]=External, PRV, FORMAT([SiteID], 0)) and SiteName_Display = IF([SiteType]=External, MASKED, [SiteName]). Then set up three roles: ExternalUsers ([SiteType] = External) to see only masked external rows, InternalUsers ([SiteType] <> External) to see internal rows with real values, and SuperAdmin to see all rows and original columns. Ensure the relationship flows DimSite - FactVisits and use masked columns in visuals so external users are protected, internal users see actual data, and admins have full access, all without blanks or confusion.

Please find the attached .pbix file 

Thank you.

[pmor]

Thanks @v-sshirivolu. It is another way to display duplicate columns but with Masked values. However, I need the external user to see all the sites' data, but to mask only the PRIV. So what they would see are SiteID_Display SiteName_Display column view with no filtering, i.e. They would see Alpha, Delta, but the rest would be PRIV and MASKED. And internals would see SiteID and SiteName, again with no fitering but with all names and IDs diplayed.

I may be able to use the Column formula method, but it would still need OLS to hide for roles and that would still mean that admin users see all columns.

[v-sshirivolu]

Hi @pmor ,
The DAX based masking approach works only at the visual layer and does not provide data security since the underlying values still exist in the semantic model, and for your scenario of keeping all rows visible while dynamically masking values per user within a single model, this is not a supported capability in Power BI, where the supported security options are Row-Level Security (RLS) for filtering data and Object-Level Security (OLS) for restricting access to tables or columns depending on the requirement.

[pmor · Accepted solution]

Thanks @v-sshirivolu for that clarification. I can move on now. Cheers.

[v-sshirivolu]

Hi @pmor ,
Thank you for confirmation, I'm happy that you resolved the isuee
Please feel free to contact us if you have any further questions. 

[cengizhanarslan]

Step 1) Create masked display columns in your dimension table

SiteID Display =
IF (
    'Dim Site'[SiteType] = "Private",
    "PRIV",
    'Dim Site'[SiteID]
)

SiteName Display =
IF (
    'Dim Site'[SiteType] = "Private",
    "PRIV",
    'Dim Site'[SiteName]
)

Step 2) Create an External role with OLS on the original columns

In Power BI Desktop → Modeling tab → Manage Roles → create a role named External. Apply OLS to hide the original SiteID and SiteName columns (and any other raw private columns) from this role. The masked display columns remain visible.

Step 3) Assign users to the correct role in Power BI Service

• External users → assigned to the External role in the semantic model security settings.
• Internal users → assigned to no role, or to a separate Internal role with no restrictions.
• Workspace admins and members bypass RLS entirely and see all data by default.

[pmor]

Thanks @cengizhanarslan. This will be my fall back solution. I would prefer admins and member users to not have these columns visible as it becomes confusing. But it may not be helped.