Power BI RLS with list of IDs

[charlottesmn]

Hello!

I have a use case where, for an embedded PowerBI report, I receive a list of IDs when calling the USERNAME() function (ie. id1,id2,id3,id4 for a user with 4 ids). A comma separates each ID.  

I had previously implemented a logic where PowerBI de-concatenates the ID and filters the table as needed. However, with more than 3 ids I am encountering performance issues and PowerBI times out.

Here is the code:

VAR currentuser = USERNAME ()
VAR isconcat = CONTAINSSTRING(currentuser, ",")

RETURN 
IF(isconcat,
CONTAINSSTRING("," & currentuser & ",", "," & [user_id] & ","),
[user_id] = currentuser)

I have also tried the following, which seems more performant but still not enough

VAR currentuser = USERNAME()
VAR UserIDs = SUBSTITUTE(currentuser, ",", "|")
RETURN
IF(
PATHCONTAINS(UserIDs, [user_id]),
TRUE(),
[user_id] = currentuser
)

Any idea on how I could solve this issue? 

Thank you

[lbendlin]

PATHCONTAINS is usually fast enough. How long is your list ?

In your example you are mixing boolean and text results. Probably not what you intended?

Your entire code should be more like

PATHCONTAINS(SUBSTITUTE(USERNAME(), ",", "|"), [user_id])

[charlottesmn]

Thank you for the insight, I will try it now.

The biggest string I am getting is a list of 8 IDs, but my user table (where the RLS is applied) had almost 200,000 unique users. It is then related to my fact table with one-to-many relationships