Substitute "Data Redacted" to a calculated column if the current user is from a specific user group?

[olimilo]

I have several levels of privacy that needs to be implemented to this report I'm working on. If they are from Group A (managers), they should be able to see the entire dataset from the dashboard. But if they are from Group B (rank and file), they should not be able to see data from certain columns. My data sources are Excel files, so unfortunately, I cannot use the Analysis Services Tabular Model dynamic masking.

To simplify this, let's say I have the following data on the left. If a user from Group B opens the dashboard, they can only see the data as it is shown on the right.

I made a table that houses the USERPRINCIPALNAME of the users for reference, then tried the following measures but I'm getting the error below. Is there some way I can use the USERNAME and USERCULTURE to check the access rights of the user?

CUSTOMDATA, USERNAME, USERCULTURE and USERPRINCIPALNAME functions are not supported in calculated tables/columns. These functions may only be used in Measures or in the AllowedRowsExpression.

Measure [Current User Access] = 
LOOKUPVALUE(
AccessRights[Access],
AccessRights[Username],
USERPRINCIPALNAME(),
"Partial"
)

Column [FacilityNameRedacted] = 
IF([Current User Access] = "Partial",
"Data Redacted",
VALUES(Data[Facility])
)

[Anonymous]

Hello-

The best way to accomplish this is through row level security. You essentially change users rights based on what they should have access to. Here is the document that explains it all.

https://docs.microsoft.com/en-us/power-bi/desktop-rls

Jared

[olimilo]

Hi @Anonymous 

Unfortunately, RLS is not applicable in my scenario. This is what RLS would do to my data: https://community.powerbi.com/t5/Desktop/Restricting-views-for-users/m-p/178633/highlight/true#M78185

What RLS will do is limit the amount of data that my users can see which is something I don't want to do. They need to be able to see the entire dataset but some columns would have to be masked with generic values if they are of a specific user group.