RLS and relations issue

[dirkmuis]

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?

[v-juanli-msft]

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

solution example

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]

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]

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]

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]

@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]

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

[dirkmuis]

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?