Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

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 EmailUser NameAgent IDLevel of Access RequiredRegionCompany
test@xxx.comTest123123IndividualUSACompany1
test@xxx.comTest456456RegionalUKCompany2
test@xxx.comTest789789NationalAUSCompany3

 

What I want to do is give a user access at a level defined in 'Level of Access Required'.

This has 3 options:

1) National which will show all data for the specific company

2) Regional which will show all data for the specific company and the region

3) Individual which will show all data for user with the Agent ID

 

This table mentioned above will then be joined on to the other datasets within the report using a many to many join relationship I imagine?

 

Let me know if any further information is required.

1 ACCEPTED SOLUTION

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 ()
    )

View solution in original post

18 REPLIES 18
sturlaws
Resident Rockstar
Resident Rockstar

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

Anonymous
Not applicable

Thanks for your reply!

I've uploaded a dummy report here: https://1drv.ms/u/s!AryWZk9AIwc0gWDGkXIsaerDX5Y3?e=wlq4Mh

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:
rlsSchema.PNG

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:
rlsManageRoles.PNG

 

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

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
Not applicable

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?

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

Anonymous
Not applicable

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?

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.

Anonymous
Not applicable

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.

Sorry, you lost me. You want to see region but not all companies in that region? And only see your own company?

 

Could you create a mockup to show your desired outcome?

Anonymous
Not applicable

So each company could have a number of regions under it for example:

 

CompanyRegion
AppleUK
AppleUSA
AppleCAN
MicrosoftUK
MicrosoftUSA
MicrosoftCAN

 

When a user is set up as 'Regional' it means that they should only see records that are in their region AND in their company as this will be shared externally. Does that make sense?


@sturlaws wrote:

Sorry, you lost me. You want to see region but not all companies in that region? And only see your own company?

 

Could you create a mockup to show your desired outcome?


 

Now that would have been pertinent information to include in your sample data file 😉

 

In the filter expression of dimCompany change this

'Access Table'[Access_Required], "National"

to

'Access Table'[Access_Required], "National"
|| 'Access Table'[Access_Required], "Regional"

And for dimRegion, change

'Access Table'[Access_Required], "Regional"

to

'Access Table'[Access_Required], "National"

 

 

 

Anonymous
Not applicable

My apologies for missing that bit of key info out!

When trying to change the DAX its throwing the error 'The number of arguments is invalid. Function CONTAINS must have a value for each specified column reference.?

could you post your code?

@sturlaws 

Thank you for the work on this.
I was studying your code, I am doing some work on RLS and wanted to learn from your technique so I added some measures to your sample to mimic the filtering and let me see what was flowing where.

More of a fast way to "View as role"

RLSSelected.jpg

 

The sample file is attached and I get the same error when making the change to the [Company Filter] measure.  My updated file is attached, I thought @Anonymous  might like to look at it as well.

 

Anonymous
Not applicable

IF(
CONTAINS(
CALCULATETABLE(
VALUES( 'Access_Table'[Access_Required] ),
FILTER( 'Access_Table', 'Access_Table'[User_Email] = USERPRINCIPALNAME() )
),

'Access Table'[Access_Required], "National" || 'Access Table'[Access_Required], "Regional"


),
'dimCompany'[Company]
IN CALCULATETABLE (
VALUES( 'Access_Table'[Company]),
FILTER('Access_Table','Access_Table'[User_Email] = USERPRINCIPALNAME() )
),
TRUE ()
)

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 ()
    )
Anonymous
Not applicable

Would like to know, what if a column should retuen all vlaues from rRequired Region when it is blank? it should consider all regions if i keep it blank. Pls suggest

Well I know what I am studying today! Smiley Happy

RLSSelected.jpg

Nicely done @sturlaws, I updated my testing file with your new code.

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.