Need help with DAX RLS filter

[DesNaidoo]

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

ID	GROUP_NAME

UserHasGroups

USER_ID

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

[lbendlin]

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]

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]

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

[DesNaidoo]

Its sure is. Quite a complex design that had to accomodate for method of provisioning entities in our App.