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
nencarnacion
Regular Visitor

Row Level Security: DAX Filtering other than username()

Hi,

 

I am new to Power Bi, please forgive my misuse of terminologies.
I am trying to implement Row Level Security for providing multilevel access for 120000 Users.
I have, so far, tried using the username() function using a sample data and it worked.

 

Now, given that the actual data that we will eventually use does not have an email column like my sample data and since i am trying to avoid medling with our client's Database, Is there a way to use, for instance, an employee ID in the DAX expression? I will have to add the Employee IDs to our AD User pool but will Power Bi see that ID and eventually use it for the Filtering?

 

Thanks for the Help!!

4 REPLIES 4
Pat_learner
Frequent Visitor

I'm facing exact same issue and I suspect there are many others in the same boat.  It is not uncommon that 'user principal name' is not part of a user's record in a given database.  User's first and last name and user id are more common.  Also, the Username() function should be consistent in its behaviour across PowerBI platforms.   Is  there a RLS workaround for  data that holds only user's name but not email address ?  Solution that calls for setting up a relathiship hard-coding user to a targeted information will not work for my environment (as any users can submit any forms.  my RLS implementation just want to set a limited view for each user such that he can only see his own list of forms he submitted when he logs in to this report in Power BI Service).  Any help will be greatly appreciated. 

 

Thanks.

Pat

v-micsh-msft
Employee
Employee

Hi nencarnacion,

 

The email address should be an attribute of the AD user account, known as UPN. For the accounts that didn't have this attribute, I don't think Username () function and RLS would work properly for those accounts.

Check the blog below for some details:

Using Username() in DAX with Row-Level Security

And: Row-level security (RLS) with Power BI

Your requirements should be similar to the following idea:

Assign USERNAME() to parameter of a SQL function data source

SQL server security only recognize the format of DOMAIN\username , please vote it if this is helpful. Or you may consider submit your idea on this aspect.

Regards

ankitpatira
Community Champion
Community Champion

@nencarnacion Unfortunately username() function in pbi service will only return logged in user's email address (and not employee id) so you wont be able to match it with emp id column of your dataset. 

Thanks for the reply ankitpatira,

 

But are there other functions I can use to return logged in user's other attributes?

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.

Top Solution Authors
Top Kudoed Authors