cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Eric_Zhang
Microsoft
Microsoft

@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

4 REPLIES 4
helkynCoello
New Member

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

@helkynCoello This is what i was looking for

 

peytonmcbrayer
Helper I
Helper I

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

@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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.