Row-level security

The user functions

USERNAME() returns the signed-in user's name, FULLNAME() their display name, and ISMEMBEROF('group') tests group membership. These evaluate on the Server against whoever is viewing.

A naive approach is [Sales Rep] = USERNAME() as a data source filter. It works, but it only handles the case where the data already contains a column matching usernames exactly.

Entitlement tables

The scalable pattern is a separate entitlement table mapping users to the things they're allowed to see:

username  | region
asmith    | East
asmith    | North
bjones    | West

Join it to your data on region, then apply a data source filter [username] = USERNAME(). One user with access to three regions gets three rows, and permissions are managed as data rather than as workbook logic.

Where to apply the filter

Apply it as a data source filter on a published data source, and lock the data source so workbook authors can't remove it. A filter that lives on a worksheet can be dragged off by anyone with edit rights.

For the strongest guarantee, push the security down into the database itself — many warehouses have native row-level security — so it holds regardless of which tool queries the data.

Testing

On Server, an administrator can preview as another user. Always test with a low-privilege account before release, and specifically test a user who should see nothing — an empty result is correct behaviour, and it's the case people forget to check.

Be aware that extracts capture whatever the extract owner could see, so entitlement filtering must apply at query time, not just at extract time, unless the extract itself is already scoped.