cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

DAX and RLS using a "security role" table

Hello !

 

I'm trying to apply RLS and can't find the right DAX formula. 

 

My data is the number of employees per location for several companies (table name CompaniesAndLocations): 

 

CompaniesandLocations.PNG

 

I want to use a "ReadAllowed " table, which allows specific users to read some companies information. The "ReadAllowed" table (real name : UsersAndCompanies) is the following (I know in real life username is SMTP address or NT username) :

 

Rightsoncompanies.PNG

 

As RLS needs a true/false condition for each line of the report, I wanted to add a column on my data table, which contains True if the companyid and username are present in the ReadAllowed table.

 

Before adding username(), I tried first to include the companyID and tried the following formula :

 

 

Calculate(countrows(UsersAndCompanies);FILTER(UsersAndCompanies; UsersAndCompanies[CompanyID]=CompaniesAndLocations[CompanyID]))>0

 

But I get an error message "A single value for column 'CompanyID' in table 'CompaniesAndLocations' cannot be determined"

 

I'm stuck with this problem. Any help is welcome 🙂

 

Laurent

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: DAX and RLS using a "security role" table

Hi @lfrancfort,

 

According to your description above, you should be able to simply create a role with the following DAX formula without creating any other calculate columns to implement the RLS in your scenario. What you need to do is creating properly relationships among your tables. For more details you can refer to this article. Smiley Happy

UsersAndCompanies[UserName] = USERNAME ()

image

 

Regards

View solution in original post

3 REPLIES 3
Highlighted
Microsoft
Microsoft

Re: DAX and RLS using a "security role" table

Hi @lfrancfort,

 

According to your description above, you should be able to simply create a role with the following DAX formula without creating any other calculate columns to implement the RLS in your scenario. What you need to do is creating properly relationships among your tables. For more details you can refer to this article. Smiley Happy

UsersAndCompanies[UserName] = USERNAME ()

image

 

Regards

View solution in original post

Highlighted
Frequent Visitor

Re: DAX and RLS using a "security role" table

Hi v-ljerr-msft,

 

You were right, my issue was related to relationships : there was a many to many relation between both tables on the CompanyID field (same company in several locations, and same company with several users linked to it). I created a Companies table, with a one to many relation with CompaniesAndLocations and UsersAndCompanies, and RLS worked fine with your simple formula.

 

Thank you

Highlighted
Microsoft
Microsoft

Re: DAX and RLS using a "security role" table

Hi @lfrancfort,

 

Great to hear the problem got resolved! Could you accept your reply above as solution to close this thread? Smiley Happy

 

Regards

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors