Forum Discussion

dirkmuis's avatar
dirkmuis
Helper 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 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?

 

Need Help

7 Replies

  • dirkmuis's avatar
    dirkmuis
    Helper II
    7 years ago

    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?

  • v-juanli-msft's avatar
    v-juanli-msft
    Community 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
      Helper 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
        Helper 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