DAX function to filter table by list of clientIDs in Role's username

[lomick2090]

Hey folks!

I want to implement some dynamic RLS on my powerbi report using some predefined roles. I am using the App Owns Data Approach. Here is my plan so far:

1. before embedding, our system will get a list of clientIDs that the logged in user should have access to.

2. That list will be made into a string deliniated by a commas

for example it could be:

'clientID1,clientID2,clientID3'
or

'clientID1'

or

'clientID1,clientID2,clientID3,clientID4' ...

3. this string will be used as the userName of the user for the embed token request

4. On the report I plan to make a role with a DAX function to filter the rows by these clientIDs.
This is basically what I have so far:

What problem I have:

I need to split this USERNAME() string by a variable number of comma deliniators, then use those separate strings inside this table to filter by. I'm having trouble coming up with a DAX equation to pull this off. Any Ideas?

[lomick2090 · Accepted solution]

We were able to figure out a solution using a DAX function as follows:

CONTAINSSTRING( CUSTOMDATA(), [platform_client_id])

This gave a True/False based on if the custom data contained the rows' id in it!

[lomick2090]

I'll try this but I think you don't understand the scenario fully

The username could be made into a list of clientID's deliniated by a commas

for example it could be:

'clientID1,clientID2,clientID3'
or

'clientID1'

or

'clientID1,clientID2,clientID3,clientID4' ...

so for all these cases we need to filter the data by these client IDs

[v-tianyich-msft]

Hi @lomick2090 ,

Does your [platform_client_id] return the same thing as username(), which will be returned in email format on the service. For a user, who has access via service, there can only be a single email, so if you have multiple comma-separated values for [platform_client_id], consider splitting them via power query.

Using Username() in DAX with Row-Level Security | Microsoft Power BI Blog | Microsoft Power BI

Split columns by delimiter - Power Query | Microsoft Learn

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[lomick2090]

[platform_client_id] in the table refers the the clientID's of all clients. And I am putting a dynamic userName() into the embed request based on the clientID's the logged in user (logged in on our app) can see.

When I implemented your solution:

[platform_client_id]=username() 

It worked for the case where the username has only one clientID, but when I want to filter by multiple ID's it fails.

I want to have a way to dynamically filter multiple ClientID's from a RLS standpoint ... ideally using a predefined role, and I don't want to have to create more roles in the future or have to update roles with extra clientID's as we get more clients.

I'm not sure if I can format the string / split by commas for this USERNAME() with power query after sending this data in through an embed request? It seems the only option for RLS is to use DAX.

[v-tianyich-msft]

Hi @lomick2090 ,

USERNAME() itself is a dynamic function. It returns different results based on the login user, and it seems like all you need to do is change the IN to “=”.

[platform_client_id]=username()

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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