topic Re: Need help with DAX RLS filter in DAX Commands and Tips

Need help with DAX RLS filter

DesNaidoo — Mon, 15 Feb 2021 09:04:18 GMT

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.

Re: Need help with DAX RLS filter

lbendlin — Mon, 15 Feb 2021 20:15:33 GMT

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?

Re: Need help with DAX RLS filter

DesNaidoo — Mon, 15 Feb 2021 23:21:14 GMT

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.

Re: Need help with DAX RLS filter

lbendlin — Tue, 16 Feb 2021 00:11:02 GMT

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

Re: Need help with DAX RLS filter

DesNaidoo — Tue, 16 Feb 2021 05:32:05 GMT

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