Power BI Embedded Row-Level Security Direct Query and Multiple values

[rebecka]

Hi,

I have a table in my report which has a row for each organization. This organization-table is used to filter all other data tables. I want to apply a RLS filter so only data for specified organizations shows. But users can have access to multiple organizations. I.e. User1 has access to Org1, Org2 and Org3. In this case I want to input "Org1|Org2|Org3" to the RLS filter.

Since I want to use DirectQuery I can't use the PATH functions. Like PATHCONTAINS(USERNAME(), [OrganizationID]) works but only for Import data.

I can't figure out how to make this work for direct query. I think I have to split the value returned by USERNAME() and then loop over that and test against each [OrganizationID] but I don't know how.

We have many organizations so there's no option in building an extra table with all possible combinations of users and organizations.

Anyone has a clue how to fix this?