cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zq Frequent Visitor
Frequent Visitor

Power BI Embedded Row-Level Security for Multiple values of single attribute

Hi all, I have embedded a power report with one role i.e. department_id. When a user accesses the report, the report is filtered according to the department_id to which he has access. This id is passed as a USERNAME() to the role using C# application. The issue here is that how a user having access to more than one departments can view the report for multile departments. I have a role DAX as [department_id] = VALUE(USERNAME()). In case of multiple department_ids, I have to use IN clause here but it is not supported in DAX. Also, I can not do it using OR conditions because the list of departments may vary from person to person. Can anyone suggest how to filter a report based on multiple department ids (the number varying dynamically)?

12 REPLIES 12
Moderator Eric_Zhang
Moderator

Re: Power BI Embedded Row-Level Security for Multiple values of single attribute

@zq

A workaround would be like using

 

VALUE(USERNAME())="XXXX"&&(
[depid] ="depid1"||[depid] ="depid2")

 

 

vinaypugalia Regular Visitor
Regular Visitor

Re: Power BI Embedded Row-Level Security for Multiple values of single attribute

Hello,

 

What I see as a solution to your problem is - 

1. Create a mapping table for Roles_Departments having columns - Role_Id & Department_Id.

2. As RLS, use the DAX - [Role_Id] = USERNAME()

3. From you C# code, pass Role_Id for USERNAME & not Department_Id

 

This way, if you develop a custom logic to find the right Role for the logged-in user, the data could easily be filtered by all the departments associated with that role.

 

Hope, this helps!

 

Thanks,

Vinay

zq Frequent Visitor
Frequent Visitor

Re: Power BI Embedded Row-Level Security for Multiple values of single attribute

How to manage the OR conditions as the number of deprtment ids may vary from user to user. Also, where is "depid1" and "depid2" coming from?

vinaypugalia Regular Visitor
Regular Visitor

Re: Power BI Embedded Row-Level Security for Multiple values of single attribute

Hello,

 

There is no need to manage any OR conditions as that is why you have the Roles_Departments mapping table there for.

E.g. Your Roles_Departments mapping tables might have values like - 

 

Role_Id | Department_Id

R1 | D1

R1 | D2

R2 | D3

R3 | D2

R3 | D3

 

Now, if you pass the particular Role_Id you need via USERNAME(), the related departments will get filtered which will inturn all the related tables showing data only related to those filtered departments.

 

Hope, this will give you more clarity. If you still have any query, please do write back.

 

zq Frequent Visitor
Frequent Visitor

Re: Power BI Embedded Row-Level Security for Multiple values of single attribute

Thanks vinaypugalia, i got your point. My previous reply was based on the comment by v-lvzhan-msft. However, I don't have roles in my scenario. Also, such table needs to be modified everytime when the user to depratment mapping changes?

vinaypugalia Regular Visitor
Regular Visitor

Re: Power BI Embedded Row-Level Security for Multiple values of single attribute

Hello,

 

I do understand that you currently do not have ROLES in your application, but you need to bring in this concept to get rid of multiple OR conditions. Also, if there is any change in User-Department mappings, any solution to come up with will need a change and having a mapping table for it will be the most scalable of them.

Anonymous
Not applicable

Re: Power BI Embedded Row-Level Security for Multiple values of single attribute

I have one table where I have to check two roles. One is "TenantID" and User Level. Can you please help with this.

 

TenantID = username() && User Level = username() 

 

Above this, I set the roles?

 

Report should be filtered via Tenant and then user level role.  

 

Please let me know how to call this role in embedded code 

 

var generateTokenRequestParameters = new GenerateTokenRequest("View", null, identities: new List<EffectiveIdentity> { new EffectiveIdentity(username: "username", roles: new List<string> { "roleA", "roleB" }, datasets: new List<string> { "datasetId" }) });

var tokenResponse = await client.Reports.GenerateTokenInGroupAsync("groupId", "reportId", generateTokenRequestParameters);

 

or 

 

{ "accessLevel": "View", "identities": [ { "username": "EffectiveIdentity", "roles": [ "Role1", "Role2" ], "datasets": [ "fe0a1aeb-f6a4-4b27-a2d3-b5df3bb28bdc" ] } ] }

 

image.png

vinaypugalia Regular Visitor
Regular Visitor

Re: Power BI Embedded Row-Level Security for Multiple values of single attribute

Hello Mayank,

 

You can probably, create a computed column which is a combination of TenantId & User Level. Once this is done, you can then pass appropriate value in the UserName(), which can then be used to apply the rule on the newly created computed column.

 

Hope, this helps!

chris-h Regular Visitor
Regular Visitor

Re: Power BI Embedded Row-Level Security for Multiple values of single attribute

Is it still the case that one needs to create another column to make this work?

 

Just to make sure I understood the question and I want to accomplish the same thing: I have a table, organizations which has a row for each organization. Organization filters is used to filter all other data tables. I want to apply a RLS filter so only data for specified organizations shows. I.e. User1 has access to Org1, Org2 and Org3. In this case I want to input "Org1|Org2|Org3" to the RLS filter.

Since I want to use DirectQuery I can't use the PATH functions. Like PATHCONTAINS(USERNAME(), [OrganizationID]) works but only for Import data.

 

I can't figure out how to make this work for direct query. I think I have to split the value returned by USERNAME() and then loop over that and test against each [OrganizationID] but I don't know how

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors