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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lfrancfort
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
v-ljerr-msft
Employee
Employee

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
v-ljerr-msft
Employee
Employee

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

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? Smiley Happy

 

Regards

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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