cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tmcdevitt Advocate II
Advocate II

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft
Microsoft

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

@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.

Capture.PNG

 

Create relationship as below.

Capture.PNG

 

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

View solution in original post

3 REPLIES 3
Microsoft
Microsoft

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

@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.

Capture.PNG

 

Create relationship as below.

Capture.PNG

 

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

View solution in original post

peytonmcbrayer Helper I
Helper I

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

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....  

helkynCoello
New Member

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

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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.