Forum Discussion

planbitionbi's avatar
planbitionbi
Frequent Visitor
5 years ago
Solved

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

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:

 

 

Need Help
Tips and Tricks
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    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

3 Replies

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    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

  • planbitionbi's avatar
    planbitionbi
    Frequent Visitor
    5 years ago

    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's avatar
      v-deddai1-msft
      Community Support
      5 years ago

      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