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
Icon for Resident Rockstar rankResident Rockstar
6 years ago

Hi Anonymous,

with your requirements, I don't think many-to-many relationships will be sufficient.

 

You could try to use the "Table filter DAX expression" feature in the "Manage roles" menu. Here is an example of how you can write the DAX:

'Sales'[Company]
    IN CALCULATETABLE (
        VALUES ( 'Sheet2'[Company] ),
        FILTER ( 'Sheet2', 'Sheet2'[userId] = USERNAME () )
    )

If you provide a sample report(upload to onedrive/dropbox/other and share the link), it will be easier to guide you through how to set it up.

regards,
S

    • sturlaws's avatar
      sturlaws
      Icon for Resident Rockstar rankResident 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

        Firstly, thank you so much you've been really helpful!

        I am 99% of the way there however I am having issues with the 'Regional' filter. Where currently if you are under the regional filter you can see your region e.g. USA but for all companies and can filter through other companies, instead of being able to only view your companies region.