RLS and overloading username() function to support multiple filter values

[tmcdevitt]

We're implementing embedded PowerBI reports for a client of our's using the "app owns data" model (i.e. we're using a service account to generate embed tokens and pass user context via the PBI REST API).

We're also leveraging RLS such that users will only be able to see data relevant to them.

An example of the payload we'd use in this case is:

{"accessLevel":"View","identities":[{"username":"foo@bar.com","roles":["Email"],"datasets":[<Dataset GUID>"]}]}

The problem is, some users actually need to be able to see not only their data but data for other users that are related to them. So, we figured we'd be able to overload the username() function by passing in a comma delimited string. So, something like this:

{"accessLevel":"View","identities":[{"username":"foo@bar.com, otheruser1@foo.com, otheruser2@foo.com","roles":["Email"],"datasets":[<Dataset GUID>"]}]}

Then, in our Role definition for "Email", we'd have something along the lines of:

[email] in {
    username()
}

However, this isn't working for us. Our reports don't break, they just don't filter properly.

Interestingly, if we do something like this:

[email] in {
   "foo@bar.com","otheruser1@foo.com","otheruser2@foo.com"
}

It actually works perfectly. So, we figured it was something about the way "username()" was returning the string. To make sure there wasn't anything wacky with it, we spit the results of username() into a Card and the resulting string looks exactly as we'd expect it to (i.e. it looks like the literal above that works).

We tried assigning the return value of username() to a variable, substituting in some double quotes and such to try to force the string to be what we wanted and that didn't matter either...

[email] in {
var rawUsername = username()
var cleanUsername = substitute(rawUsername, ",", """,""")
return cleanUsername
}

Anyway, what we're trying to accomplish doesn't sound crazy but we just can't get this to work the way we want it. We assume there's some sort of magic going on in the username() function that we're just not understanding.

Any help would be much appreciated.

- Terence

[helkynCoello]

hello

I had the same issue: I need to filter by powerBI report for a user to view just data for certain offices; and this worked for me:

1) in power bi desktop--> Manage roles, out this expression:

PATHCONTAINS(
var pList = USERNAME()
return pList
,[officeID]
)

2) then when invoking the embedtoken endpoint from power bi api , i pass the list of offices like this: "1|4|52|12"

Works really nice..hope it helps!

Helkyn C

[Elsheba]

This solution was exactly what we needed. Thank you so much! 

[kjmusser4635678]

This should be the "solved" answer, it is not true there is no solution, this works !

[Surya9]

@helkynCoello This is what i was looking for

[peytonmcbrayer]

I am trying to achieve the exact same thing, and I have the same issue. I don't know why this is marked as resolved.   Did you come up with an actual working solution?   I've tried everything.   If I put a hard-coded list of data in the user role filter, it works fine, but if I try to wrap the username() function in brackets, the RLS filter does not work at all.   I did try to use the filter with a single item plugged into the "Other User" field in "View as Role" in PBI desktop, with NO QUOTES, and it works fine.   There must be something with how the username() function is interpreted in the DAX expression that breaks it....  

[Eric_Zhang · Accepted solution]

@tmcdevitt

AFAIK, it is not possible to overload the username(0 function. You may have to apply below workaround when modeling in Power BI desktop.

Maintain a mapping table as below.

Create relationship as below.

Apply RLS to Table RLSUsers and assign user(User1, User2) when generate embed token.