Getting error when using USERPRINCIPALNAME() to filter content as part of a calculated column

[olimilo]

I have a table that contains User IDs and an Region value for the data that they can access in our dashboard.

Name	Department	UserID	RegionAccess
Jane Doe	Operations	12345	Global
John Doe	Training	12346	EMEA
June Doe	Operations	12347	EMEA

Basically, how we would like this to work is, we will pull the current user's RegionAccess value using their USERPRINCIPALNAME() from this table and filter the master data according to their ID's equivalent RegionAccess value. The IDs are not (and cannot) be found in our master data so this is the only way for us to filter this out.

I tried making a custom column with the following formula however I am getting a usage error with the USERPRINCIPALNAME value:

Column = CONTAINSSTRING('MasterData'[Region]), LOOKUPVALUE('UserData'[RegionAccess], 'UserData'[EmailAddress], USERPRINCIPALNAME()))

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.

I tried using the same DAX expression as the RLS table filter instead however it doesn't seem to be working as intended because when I use View As another user, the data is not filtered. Is there some other approach I can do here? I was able to do something similar on a PBI Paginated Report so I was hoping I could replicate it here.

[Anonymous · Accepted solution]

@olimilo 
You cannot use USERPRINCIPALNAME () in a column, because this function returns a dynamic result based on your login account. 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@olimilo , I think you can use USERPRINCIPALNAME in a measure, not in a column

[AllisonKennedy]

@olimilo Have you typed an email into the view as roles box? You need to tick both the top option with the text box -write your email or a sample user email in that box, then ALSO tick the box next to the role you wish to test.