Security Filter by Country

[dshetler12]

Hello,

I have an app that I would like to filter by approved countries by user.  In my Training_Teams table I have a Country Column that is populated by a single value.  In my User_Approved_Regions table I have an email column (UserEmail), and an enumlist column of countries (Approved_Countries).

I'm trying to check that the country from the Train_Teams table is in the list of the current users approved countries.  Below is the code I'm using to generate the Y/N for each line in the security Filter.

The strange thing is that when I hit the Test button I get the results I want, but when I save the filter I get no data in the app.  I've confirmed that the email I'm signed in as matches the email in the User_Approved_Regions table.

IN([Country],SPLIT(CONCATENATE(SELECT(User_Approved_Regions[Approved_Countries],[UserEmail]=USEREMAIL())),","))

I've tried several variations of using split, concatenate, and list, but so far I get the same results.

Thanks for the help!

[dbaum · Accepted solution]

---

@dshetler12 wrote:

I've tried several variations of using split, concatenate, and list, but so far I get the same results.

---

Did you try without these list manipulations? The [Approved_Countries] value from a single user's row is already a list. For example, maybe the following would work (potentially even without the INDEX function).

IN(
    [Country],
    INDEX(
        SELECT(
            User_Approved_Regions[Approved_Countries],
            [UserEmail] = USEREMAIL()
        ), 
        1
    )
)