Forum Discussion
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):
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) :
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
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. :smileyhappy:
UsersAndCompanies[UserName] = USERNAME ()
Regards
3 Replies
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. :smileyhappy:
UsersAndCompanies[UserName] = USERNAME ()
Regards
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
Hi lfrancfort,
Great to hear the problem got resolved! Could you accept your reply above as solution to close this thread? :smileyhappy:
Regards
