Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
@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.
@olimilo , I think you can use USERPRINCIPALNAME in a measure, not in a column
@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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
User | Count |
---|---|
98 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |