With Users table and RLS, data is limited if no user is associated to another table

[AlanJacobson]

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

[AlanJacobson · Accepted solution]

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 )