Filter by concatenated columns for RLS

[Anonymous]

Hi everyone

I have the following security table(sql) for my rls, I want to filter a table based on this 

UserPrincipalName	Site
usu1@example.com	s1,s3,s6
usu2@example.com	s2,s19,s23
usu3@example.com	s3,s4

but, I couldn't filter by this Site column

and the next table is what i want to filter based on this security table

Region	Country	Site
A	a	s2
A	b	s1
B	c	s19
B	d	s3
C	e	s4

 I think I was getting an error due I am using an IN operator but what my Site is returning me is kind of this "s1,s2,s3", so which will be the best option to do it?

Thank you in advance.

[jdbuchanan71 · Accepted solution]

@Anonymous 

Your filter expression could be something like this (you will need to change the names of the tables to match your model).

VAR _UPN =
    USERPRINCIPALNAME ()
RETURN
    CONTAINSSTRING (
        LOOKUPVALUE ( Users[Site], Users[UserPrincipalName], _UPN ),
        Sites[Site]
    )

And you would apply that to the Sites table.

Although there is a weakness in doing it this way because PowerBI sees s2 in 's1,s23' so it is giving a false match.

If that is really how your site ID's are you may need to put in leading 0's  's02' instead of 's2' or you could split the sites in the users table then unpivot them to get 1 row per user/site pair.

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

When  we see the result as usu2@example.com,we should see S2,S19 and S23, however in jdbuchanan71's reply we can see S1.

You may try my way, transform your User Table and build an RLS.

Splite Site column in rows in Power Query Editor.

Build a relationship between two site columns in two tables.

Try this Dax expression.

[Site] = 
CALCULATE (
    MAX(Data[Site]),
    FILTER (
        Users,
        Users[UserPrincipalName] = USERPRINCIPALNAME()
            && Users[Site] = Data[Site]
    )
)

 See the result as usu2@example.com.

Result:

You can download the pbix file from this link: Filter by concatenated columns for RLS

Best Regards,

Rico Zhou

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

[jdbuchanan71 · Accepted solution]

@Anonymous 

Your filter expression could be something like this (you will need to change the names of the tables to match your model).

VAR _UPN =
    USERPRINCIPALNAME ()
RETURN
    CONTAINSSTRING (
        LOOKUPVALUE ( Users[Site], Users[UserPrincipalName], _UPN ),
        Sites[Site]
    )

And you would apply that to the Sites table.

Although there is a weakness in doing it this way because PowerBI sees s2 in 's1,s23' so it is giving a false match.

If that is really how your site ID's are you may need to put in leading 0's  's02' instead of 's2' or you could split the sites in the users table then unpivot them to get 1 row per user/site pair.