How to convert USERPRINCIPALNAME() from a measure to a calculated column

[ldwf]

Hello, is it possible to convert the USERPRINCIPALNAME() from a measure to a calculated column? Or somehow create a calculated column that would be the email address of the person logging in? I would like to be able join the email of the person logging in to another table that has email address..  Thanks

[jdbuchanan71 · Accepted solution]

If you create the model in a .pbix and add the roles then publish it to the service you can then use that model as a data source for other reports and the roles will already be in the model.  You can also assign the AD groups to the role so any new user added to the groups would automatically get the role applied to them.

[jdbuchanan71 · Accepted solution]

If you create the model in a .pbix and add the roles then publish it to the service you can then use that model as a data source for other reports and the roles will already be in the model.  You can also assign the AD groups to the role so any new user added to the groups would automatically get the role applied to them.

[ldwf · Accepted solution]

That makes perfect sense.  Appreciate the help!

[jdbuchanan71]

@ldwf 

It's not possible to have a calculated column with USERPRINCIPALNAME().  Calculated columns are set when the model is refreshed, USERPRINCIPALNAME() is determined at runtime for lack of a better term. 

If you are trying to apply filter for row level security you can apply a filter to the table that has the email address with a measure that uses USERPRINCIPALNAME().  That will get applied at runtime and is the basis of RLS.

[ldwf]

Thanks for the response.  If I understand correctly, your solution involves creating a role.  I am looking for a solution that doesn't involve roles, as my understaning is that using roles means I would have to do this for any report I publish.  I was hoping to have something built in my model one time where I can join the UPN (i.e, the user logging in) to a table that has email address and AD Group, and then join the AD Group to another table that has AD Group and an indicator column to indicate what they have access to, which would join to a fact table that has this indicator.  hope this makes sense.  Is there any way to maniupulate the userprincipalname() field to convert it to string?