Forum Discussion

DesNaidoo's avatar
DesNaidoo
Frequent Visitor
5 years ago

Need help with DAX RLS filter

Hey Guys,

 

Hope someone can help. I know I sound like a noob...but, I would like to filter one table with values from another table. I am setting up RLS that filters the tables based on the current user (Power BI Embedded into an app)

 

I have two tables Groups and UserHasGroups

Groups

IDGROUP_NAME

 

UserHasGroups

USER_IDGROUP_ID

 

UserHasGroups[GROUP_ID] = Groups[ID].

In the Roles, I have a DAX filter that filters the UserHasGroups to only display the groups for the current user:

 

 [USER_ID]=
            LOOKUPVALUE(
                USER[ID],
                USER[USERNAME],
                USERNAME()
            )

 

 What I would like to accomplish is to thereafter filter the Groups table to only include IDs that exists in the filtered UserHasGroups table. 

 

can anyone please help me with this?

 

Thanks.

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    1. please do not use USERNAME(), it behaves differently on the desktop and in the service. Use USERPRINCIPALNAME()

    2. Normally this type of RLS is implemented through the data model (ie the UserHasGroups table sits upstream from the Groups table and filters it automatically).  Is that something you can consider?

    • DesNaidoo's avatar
      DesNaidoo
      Frequent Visitor
      5 years ago

      Thanks lbendlin for your reply
      I will consider using the USERPRINCIPALNAME().
      The problem is, I have bridge tables and UserHasGroups is sitting on the many side of the relationship. 

      • lbendlin's avatar
        lbendlin
        Super User
        5 years ago

        That is indeed a problem. What's the reason for that setup?