Forum Discussion
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.
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?
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: