Forum Discussion
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 related to states with security:
Users can have a Role of "ViewAll" or "Limited". Dax formula allows "ViewAll" to see all data:
So when I view as limited user I only see the one state:
BUT when I view as ViewAll role, I don't see every state. I only see states that have a record in the Users table. Because no user is associated to Wisconsin, I cannot see the WI cities:
So the absence of "WI" in the Users table prevents a "ViewAll" user from seeing those Wisconsin cities. How can I structure this so all cities appear for ViewAll?
Geoff
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 )
1 Reply
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 )