cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dirkmuis Helper II
Helper II

RLS and relations issue

So I have set-up tables for RLS as advised here.

I have a user and usergroup table. And a test user in both of them.

 

But I'm strugling with the rest of my relations.

User has the RLS off course on the username ([tblBIusername] =userprincipalname()). So that relation is single.

If I test with multiple records I indeed only see myself.

 

In the Userprofgroup table I've my username with an ID.

The relationship to courserequest is set to both with the security filter on.

I do see only relevant records on that table.

 

Now the courserequest is linked via courseID to the tblcourse. I'd like to only receive data form those courses which I'm authorized to via the courserequest. So my first idea was to just put the normal relation there (many to one) single. But I was still seeing the full table. I've also tried the both directions which didn't help a lot. All other combinations do give errors.

So how do I make sure that I'm only seeing the right course info related back to the user?

 

RLS_question.PNG

7 REPLIES 7
dirkmuis Helper II
Helper II

Re: RLS and relations issue

Very strange thing is that when I add the table visual and add courseID from courserequest I only see the relevant data. If I add courseID from tblcourse I see all data.

 

So does that relation for some reason only work when you add the FK from the underlying table?

Community Support
Community Support

Re: RLS and relations issue

Hi @dirkmuis 

Here is an article regarding issue on "Dynamic Row Level Security with Profiles and Users in Power BI : Many-to-Many Relationship".

It explain the reason why RLS don't work under this similar scenario as yours.

Please read and check the solution on your side.

if it doesn't help, please give me an simple data example so i can reproduce and work on your issue.

 

wrong example

1.png

solution example

2.png

 

Best Regards

Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

dirkmuis Helper II
Helper II

Re: RLS and relations issue

Thanks that helps a lot. However, the first solution (bi-directional) doesn't let the DAX through to the latest table in my sample pbix?

I couldn't get the DAX for the 2nd solution to work?

 

[CourseID] IN SELECTCOLUMNS(
	FILTER(
	'Usergroup',
	RELATED(User1[Username])="test@test.com"
),
	"Group"
,[Group]
)
dirkmuis Helper II
Helper II

Re: RLS and relations issue

Guess it should be:

 

[Group] IN SELECTCOLUMNS(
FILTER(
'Usergroup',
RELATED(User1[Username])="test@test.com"
),
"Group"
,[Group]
)


However this does filter the Coursegroup table but not automatically the course table. My relations seem to be a little different a well. I could fix this with putting an extra DAX code on the Course table but is this the correct way forward?

 

[CourseID] IN SELECTCOLUMNS(
FILTER(
'CourseGroup',
RELATED(Usergroup[Group])=[Group]
),
"CourseID"
,[CourseID]
)


So on the user table I'm filtering with dynamic RLS on the userprincipalname() (for testing hardcoded of course).

On the Coursegroup table I'm relating with dynamic RLS to the correct group.

On the Course table I just have to relate back to Coursegroup?

 

See:

https://1drv.ms/u/s!AktsOVneXVdRjL1VV9SqMZYhqAkHqw

dirkmuis Helper II
Helper II

Re: RLS and relations issue

And if the solution in my previous post is correct, the question will be if that's the best way forward, or providing powerBI with flat tables is better. We don't have any DAX experts and looking for basic powerBI dashboards. Creating al kind of DAX logic could become confusing over time while keeping the logic within SQL views would be easier.

 

On the other hand, creating the relations in powerBI gives us the option to not send all the data out multiple times. If it's only 1 or 2 dimensions and so 1 or 2 DAX rules it won't be that hard I guess.

dirkmuis Helper II
Helper II

Re: RLS and relations issue

@v-juanli-msft 

I just found out that this doesn't work either because you're only relating 1 level.

So in my example when you add multiple users in the usergroup the Course table will show records for all users.

 

See this sample:

1. 2 users in the usertable

2. for each user one or more records in usergroup

3. usergroups are related to coursegroups

4. courses related to coursegroups

 

So for 1,2 and 3 the data will be filtered correct of I act as 1 user. 

However the course table will show all records for all users in the usergroup table. I guess that's because you're not relating back to the original user table which means you need to filter inside a filter to get this done? 

How would that syntax look like?

 

https://1drv.ms/u/s!AktsOVneXVdRjL1WAe2UNW9HzVVfLw

dirkmuis Helper II
Helper II

Re: RLS and relations issue

Sorry about all the posts but I did a lot of testing....

What did work eventually for me is get rid of all the DAX except the first one on username=userprincipalname().

 

I didn't put any checkboxes as long as the relating flows through as visualized by the arrows on the relation.

For the last one it's a many to one so the RLS got blocked there. I changed the relation to BOTH and ticked the checkbox and voila, it works.

 

But the idea was to not use that checkbox because of performance reasons. So my question is still if there is an other way to accomplish this same result:

 

https://1drv.ms/u/s!AktsOVneXVdRjL1XhrjXm5Zx0cRKEg

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors