Row Level Security question

[ToddChitt]

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 statements like 'Client'[Primary] = USERNAME(). A Sales Person may be listed as the Primary on one client record and the Secondary on another. They would be members of both security groups so would be able to see Clients where they are listed as Secondary. There are cases (on purpose) where someone may be listed as BOTH the Primary and the Secondary.

I would like to have some mechanism whereby users can apply a filter so they see only those rows where they are listed as the Primary. I would also apply this filter on a global basis before pinning any of the measures to the Dashboard, prior to publishing the App. In other words, when users hit the App and Dashboard, the DEFAULT view/filter should be only data where they are listed as the Primary.

I tried creating a Calculated Column in the Client table with the statement of:

Is Primary = 'Client'[Primary] = USERNAME()

But I got an error message saying that I can't use USERNAME() function in a column.

I could create specific measure that filter the data for only the case above, but I have about 50 measure in the model. 

I have about 100 users (Sales People) that will access the published App on Power BI Service so the solution needs to be dynamic and all-encompassing.

Any suggestions out there?

Thanks in advance.

[FrankAT]

Hi,

you have to implement dynamic row level security inside the datamodel.

Take a look at the following youtube video (Power BI Dynamic Row Level Security 1):

https://www.youtube.com/watch?v=iMfkj_xgYYc&list=PLRKSzkvyfSLe2gZagN8DYnXiLMV52jDp9&index=7

Regards FrankAT

[ToddChitt]

Sorry, @FrankAT but that doesn't help. 

I am well versed in Dynamic Row Level Security in Power BI. I have it configured in many models, inclouding this one.

Perhaps a drawing or two will help explain my goal.
As stated before, any one user may be listed in the [Primary] field and/or the [Secondary] field. There is a Role defined for each of these two cases. Some users are listed a MEMBERS in both Roles. So a User may have access to see data as represneted by the following Venn diagram, where one circle represents those Clients where he/she is listed in the [Primary] column, and the other circle represents those clients where he/she is listed in the [Secondary] column. The FULL list of Clients that he/she will see is the COMBINATION of BOTH blue circles.

BUT, what I WANT is to have a way to filter the data for ALL users so that they only see Clients where they are listed in the [Primary] column, as represneted by the one colored circle below. It would be a SUBSET of all the data to which they have access.

IF I could use the USERNAME() or USERPRINCIPALNAME() function in a Calculated Column, this would be a non-issue. But I can't, so I'm asking the community if anyone has encountered and solved this.

Thanks.

[v-frfei-msft]

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]

@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 "bob@mydomain.com" 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 "bob@mydomain.com" 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.

[ToddChitt]

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.