Forum Discussion

nencarnacion's avatar
nencarnacion
Regular Visitor
9 years ago

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!!

Need Help

4 Replies

  • ankitpatira's avatar
    ankitpatira
    Community Champion
    9 years ago

    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. 

    • nencarnacion's avatar
      nencarnacion
      Regular Visitor
      9 years ago

      Thanks for the reply ankitpatira,

       

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

  • v-micsh-msft's avatar
    v-micsh-msft
    Microsoft Employee
    9 years ago

    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

  • Pat_learner's avatar
    Pat_learner
    Frequent Visitor
    8 years ago

    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