security filter

[Cannone_Cannone]

Hi everyone
I need your precious help, I have this formula that I use in a security filter that works very well."IN(ANY(SELECT(User[Area], [Email] = USEREMAIL())),LIST([Area]," All areas"))"
I need to show who in the “AREA” column has as value RMP,CLN only the data of that area and “BHS,L&F”

In the first image it is how it is working now, in the second image it is how I would like it

it’s possible to do it? Thank you

[Joseph_Seddik]

1. Have a slice on your shown table, name it currentUser, with the following row filter condition: USEREMAIL() = [Email]
2. Change the type of your “Area” column to EnumList.
   Ideally you’d also have a table for Areas, and your EnumList “Area” column should have a base type Ref pointing to that Areas table.
3. Your Security Filter expression then would be:
   OR(
   IN(“All areas”, ANY(currentUser[Area])),
   IN([Area], ANY(currentUser[Area]))
   )

[AleksiAlkio]

You can add those values to your list like LIST([Area],“All areas”,“RMP,CLN”,“BHS,L&F”)

[Cannone_Cannone]

Hi, thanks for the help, but I think I made a mistake, it doesn’t work because in the area column of the application I only have SINGLE VALUES, for example RMP-CLN-BHS, am I the one who merges them into the AREA column of the “User” table, correct?

[AleksiAlkio]

So.. you would like to use an Enumlist column in “Users” table so the user would have access for multiple areas?

[Joseph_Seddik]

@Cannone_Cannone

in the area column of the application I only have SINGLE VALUES, for example RMP-CLN-BHS, am I the one who merges them into the AREA column of the “User” table

That’s why I told you to change it to EnumList, then you follow the rest of the steps, and it will work.

[Cannone_Cannone]

Hi, thanks for your help, maybe I’m doing something wrong?

[Joseph_Seddik]

OK, we’ll take it step by step. First let’s have an “Areas” table. Can you build that table with at least just two columns? A key and an Area label (where you put your accronyms: CLN, RMP, etc.)?

Would you please have this table and show it to me?

[Cannone_Cannone]

Hi, thank you, for now I have this, user table and excel sheet called user

[Joseph_Seddik]

@Joseph_Seddik

First let’s have an “Areas” table. Can you build that table with at least just two columns? A key and an Area label (where you put your acronyms: CLN, RMP, etc.)

Now let’s have this another table please. Each acronym will be in its own row.

[Cannone_Cannone]

Hello done!! correct?

[Cannone_Cannone]

Good morning Joseph, can you please give me a hand?

Thanks a lot!!

[Joseph_Seddik]

Thanks and sorry for late reply.

Now please make the “AREA” column of your new table a Label for this table.

Then back to your Users table, please do the following:

1. Add a new column, name it for example: “areaFlag”, with: Display Name: “All Areas?”, type Yes/No, and make it Required.
2. Change the type of your “Area” column to EnumList, with a base type Ref pointing towards the AREA table.
3. Also for the “Area” column:
   • Show?:
     AND(ISNOTBLANK([areaFlag]), NOT([areaFlag]))
   • Initial Value:
     IF([areaFlag], AREA[Key], LIST())
   • Valid If:
     IF([areaFlag], [_This] = AREA[Key], NOT([_This] = AREA[Key]))

Please tell me once you do this and we’ll continue with next steps. Thanks.

[Cannone_Cannone]

Hi, thank you

I think I did everything right

[Cannone_Cannone]

Hi, have you forgotten about me?