Forum Discussion
Solved
With Users table and RLS, data is limited if no user is associated to another table
StateCode joins these tables: I ONLY have users for 2 states, MN and TX: Even though there's a few states: And a few cities joined to states: Users are r...
I solved by removing security from the Users -> States relationship and applying a Role dax filter only to the States table directly. In that expression I can check for role and state for the current user, then apply those values.
// get the current state code VAR _stateCode = MAXX( FILTER( 'Users', 'Users'[EmailAddress] = USERPRINCIPALNAME() ) , 'Users'[StateCode]) // current role VAR _roleCode = MAXX( FILTER( 'Users', 'Users'[EmailAddress] = USERPRINCIPALNAME() ) , 'Users'[Role]) // if ViewAll, the user can see all the data, otherwise limit data based on logged in email address -> state code RETURN IF( _roleCode = "ViewAll", TRUE(), [StateCode] = _stateCode )
I solved by removing security from the Users -> States relationship and applying a Role dax filter only to the States table directly. In that expression I can check for role and state for the current user, then apply those values.
// get the current state code
VAR _stateCode =
MAXX(
FILTER( 'Users', 'Users'[EmailAddress] = USERPRINCIPALNAME() )
, 'Users'[StateCode])
// current role
VAR _roleCode =
MAXX(
FILTER( 'Users', 'Users'[EmailAddress] = USERPRINCIPALNAME() )
, 'Users'[Role])
// if ViewAll, the user can see all the data, otherwise limit data based on logged in email address -> state code
RETURN
IF(
_roleCode = "ViewAll", TRUE(),
[StateCode] = _stateCode )