Forum Discussion

ToddChitt's avatar
ToddChitt
Super User
6 years ago

Row Level Security question

Hello all, I have a model with 2 columns in the Client table: [Primary] and [Secondary], both have email addresses of the Sales Person. I have two security roles defined for each column with stateme...
Need Help
v-frfei-msft's avatar
v-frfei-msft
Community Support
6 years ago

Hi ToddChitt ,

 

We cannot use the function USERNAME to create calculated column.We can create roles like that.

 

Also you can refer to the online document.

 

If it doesn't meet your requirement, Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

 

 

 

 

 

  • ToddChitt's avatar
    ToddChitt
    Super User
    6 years ago

    I am well versed in RLS in Power BI. 

    To state the problem another way:

    I have a large user population with access to the app so the dashboards need to be dynamic.

    Any one user may have 'rights' to 'see' any one row in the major table by virtue of the fact that there are TWO different Roles and each Role secures the table via a DAX Filter statement on one of TWO different fields, I want to default the Dashboard tiles to show only the data that is attached to rows where the ONE role is in play.

     

    IF the USERNAME() function could be used in a Column definition, then I would be all set. So I'm asking if anyone has done this before and if anyone has suggestions.

     

    Unfortunately, this is proprietary data AND is in an on-premise SSAS Tabular database so cannot be shared. It can only be described, sorry.

  • ToddChitt's avatar
    ToddChitt
    Super User
    6 years ago

    v-frfei-msft 

    I have created a simple model with four rows of data available here:

    https://www.dropbox.com/s/j0i3zl0rgsf1qx5/Sample%20RLS.pbix?dl=0

     

    The user "[email protected]" has access to see three of the four rows of data: Clients ONE and TWO (because he is listed in the [Primary] column), and Client FOUR (because he is listed in the [Secondary] column. If you do a "View As" and select both Primary and Secondary roles, then select Other and put in "[email protected]" as the user, you will see those three rows. 

     

    But here is the rub: I want to create a Dashboard that is universal to all the bobs in my company where the tiles are filtered for ONLY the data rows where the loged in user is listed in the [Primary] field. In other words, when bob logs in and accesses the Power BI App, he should see ONLY Clients ONE and TWO, with a Measure total of 300. 

     

    Thanks for any help or suggestions you can supply.