Filter by concatenated columns for RLS

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.

Hi Anonymous 

When  we see the result as [email protected],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 [email protected].

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.