Forum Discussion

charlottesmn's avatar
charlottesmn
Frequent Visitor
1 year ago

Power BI RLS with list of IDs

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

Need Help
Tips and Tricks

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    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's avatar
      charlottesmn
      Frequent Visitor
      1 year ago

      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