Forum Discussion

dirkmuis's avatar
dirkmuis
Icon for Helper II rankHelper II
7 years ago

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...
Need Help
v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
dirkmuis
Icon for Helper II rankHelper II
7 years ago

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])="[email protected]"
),
	"Group"
,[Group]
)
  • dirkmuis's avatar
    dirkmuis
    Icon for Helper II rankHelper II
    7 years ago

    Guess it should be:

     

    [Group] IN SELECTCOLUMNS(
FILTER(
'Usergroup',
RELATED(User1[Username])="[email protected]"
),
"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's avatar
      dirkmuis
      Icon for Helper II rankHelper II
      7 years ago

      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's avatar
        dirkmuis
        Icon for Helper II rankHelper II
        7 years ago

        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