0

I am working on a PowerBI report which requires RLS.

To simplify, let's say I have the following tables:

Table 1: Sales, Account, ProductOwnerAccount, Owner, Region

Table 2 Userid, UserRegion, UserAccount

Scenario: Need to show two sets of tabular data:

  1. All Rows Where Account = UserAccount and Region = UserRegion
  2. All Rows where ProductAccount = UserAccount, Account <> UserAccount and Region = UserRegion

The only way to achieve this seems to be RLS and using two roles with corresponding DAX filters.

However, if I apply two roles to the same user, the least restrictive one takes precedence and hence, both report will give the same result.

I tried to create a calculated table, but that does not allow using USERPRINCIPLENAME as a filter.

I also tried to have a page level filter which could use a measure (which in turn uses USERPRINCIPLENAME()), this is also not allowed.

Similarly, a calculated column on each row to specify if it's owned by the current user doesn't work.

Is there any other way? Am I missing something very basic?

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
SKocheta
  • 133
  • 1
  • 8
  • What *do* you want to show for a user that is in both roles? – Alexis Olson Mar 08 '18 at 16:02
  • The ask is for a user to be able to see both above scenario. I could think of this to be possible only via rls. So if a user is assigned both roles,he would see two visuals one for each scenario – SKocheta Mar 08 '18 at 17:09
  • Can you duplicate Table 1 and the report page and have two tabs, one for each scenario? – Alexis Olson Mar 08 '18 at 19:10
  • That's what I have done currently, but it felt way roo crude. We are unnecessarily duplicating the data. I was hoping for a filter based on metrics or a calculated table or something similar, which does not ask for duplication. – SKocheta Mar 08 '18 at 19:24
  • I see. Is security regarding what the user sees particularly important or are you just using RLS to shape the configuration? I think it may be possible without duplication if you don't care if the user can possibly gain access to all of the data. – Alexis Olson Mar 08 '18 at 19:28
  • The target users have access to entire data, however, more than the security... If they gain access to entire set here, they loose the business insight at granular level. The idea is to compare product performance vs account performance. – SKocheta Mar 08 '18 at 19:38
  • It is possible to use `USERPRINCIPLENAME()` inside of measures. One approach is to apply filtering there instead. See my answer [here](https://stackoverflow.com/questions/49091456/default-filter-in-power-bi-row-level-security-not-applied-through-lookupvalue/49122726#49122726) for an example. – Alexis Olson Mar 08 '18 at 20:16
  • Let me understand this approach and try,thanks – SKocheta Mar 10 '18 at 09:39

0 Answers0