Row level security with user email -> Multiple User ID with same e-mail to filter on

[planbitionbi]

Hi all!

I have succesfully implemented row level security using the guide:

https://www.youtube.com/watch?v=Sge_g9hTXWE&feature=emb_logo&ab_channel=PragmaticWorks

Now the only issue is that I can have multiple users (userID's) with the same email adress and my DAX Filter on the table I have implemented RLS on expects a singel UserID. How can I change this DAX Formula in a way that multiple USerID's can be the input of the filter.

DAX Formule on my CustomerStructure table:

[ID] IN SELECTCOLUMNS(
FILTER(
Security_User_CustomerStructure,
Security_User_CustomerStructure[PersonID] = LOOKUPVALUE(Security_Users[PersonID], Security_Users[Email], USERNAME() )
),
"ID", [CustomerStructureID]
)

Printscreens:

[planbitionbi]

Hi!

Thanks a lot for your feedback. Unfortunately it's throwing an error like below. Can you see why it's wrong? Something with the IN clause:

[v-deddai1-msft · Accepted solution]

Hi @planbitionbi ,

Please refer to the following measure:

[ID]
        IN SELECTCOLUMNS (
            FILTER (
                Security_User_CustomerStructure,
                Security_User_CustomerStructure[PersonID]
                    IN CALCULATETABLE (
                        VALUES ( Security_Users[PersonID] ),
                        FILTER ( Security_Users, Security_Users[Email] = USERPRINCIPALNAME () )
                    )
            ),
            "ID", [CustomerStructureID]
        )

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[v-deddai1-msft]

Hi @planbitionbi ,

I suggest you use USERPRINCIPALNAME ( ) instead of USERNAME() and you can change the dax to :

[ID] IN SELECTCOLUMNS(
FILTER(
Security_User_CustomerStructure,
Security_User_CustomerStructure[PersonID] IN CALCULATETABLE(VALUES(Security_Users[PersonID]),FILTER(Security_Users,Security_Users[Email] = USERPRINCIPALNAME())),
"ID", [CustomerStructureID]
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai