cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tmcdevitt Regular Visitor
Regular Visitor

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
Moderator Eric_Zhang
Moderator

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.

2 REPLIES 2
Moderator Eric_Zhang
Moderator

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.

Highlighted
peytonmcbrayer Regular Visitor
Regular Visitor

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

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 129 members 1,683 guests
Please welcome our newest community members: