Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX Code for Dynamic RLS

I am new to DAX and am trying to implement some dynamic RLS. The current table of users details looks like below: User Email User Name Agent ID Level of Access Required Region Company tes...
Need Help
  • sturlaws's avatar
    sturlaws
    6 years ago

    sorry, was a bit quick there, you need to write it like this

    VAR _t =
    CALCULATETABLE (
        VALUES ( 'Access_Table'[Access_Required] ),
        FILTER ( 'Access_Table', 'Access_Table'[User_Email] = USERPRINCIPALNAME () )
    )
RETURN
    IF (
        CONTAINS ( _t, 'Access Table'[Access_Required], "National" )
            || CONTAINS ( _t, 'Access Table'[Access_Required], "Regional" ),
        'dimCompany'[Company]
            IN CALCULATETABLE (
                VALUES ( 'Access_Table'[Company] ),
                FILTER ( 'Access_Table', 'Access_Table'[User_Email] = USERPRINCIPALNAME () )
            ),
        TRUE ()
    )
sturlaws's avatar
sturlaws
Resident Rockstar
6 years ago

The sample you provided is a bit short on data, so it is not really suited to see the full effect of RLS. But this how I would set it up

1. Change up the model to look more like a star model:


2. Click on the manage roles-button, and create a role called e.g. all. Then on the tables which are prefixed with dim, add the dax statement which will filter the table according to the access level they have according to Access Table:

 

3. You can then test your role like this, add the e-mail of the user you want to test for, and click 'OK'

When you publish it to power bi services, you need to go to the Dataset section in the left menu, and for the dataset of your report, click the three dots and find Security. You should have a ad/security group called something like Everyone or All, add this group to the role you have created.

 

pbi desktop file

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi, sorry to be bringing this post back to life.

When publishing the report to PBI Service I understand you have said "You should have a ad/security group called something like Everyone or All, add this group to the role you have created."


Can you provide further information on what you mean by this and what needs to be done? Currently I can see the ALL option but not sure what email I meant to add to that list?

  • sturlaws's avatar
    sturlaws
    Resident Rockstar
    6 years ago

    Are you asking about how to add users and groups to a role? Or why you should use All/Everyone?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      So, would I have to add every user that is in the access table to this group? The idea was that this report would be shared externally and then the external companies would be updating a file that would populate the access table. Which I was hoping would mean I wouldnt have to add 100's of users to the report. Is this not the case?

      • sturlaws's avatar
        sturlaws
        Resident Rockstar
        6 years ago

        From what I have read, if a user is not assigned to a role, they will not see any data in the report. But I have not been able to verify this on my own, getting some errors I don't understand. If you like I can send you my e-mail adress, and we can test what it looks like if my adress is not assigned to any role.

         

        If it is nesseccary to add user to a role in order to see the report, it is possible to create dynamic groups in Azure Active directory. It should be possible to build a group for everyone in your domain and for external users. Then add this group to one or more of your roles. Anyone using you report will then be filtered by the RLS.