Problem with 2 reports connected to common dataset with 2 RLS statements

[PiotrStawicki]

Hi!

I have a problem that seems to be easy, but already has taken me hours and I haven't find any solution also in the forum (there are similar issues described but I didn't find anything that would help me).

I have an endorsed dataset with employees statistics and 2 live-connected reports:

1. Team report - for managers (they can see their own data + all the people down the hierarchy by paths established in DAX + RLS)

2. Personal report (both normal employees and managers should see only their own data here).

If I create 2 separate roles and assign people to them, managers can always see the data of whole team in both reports. Having 1 role with different filtering on two supportive tables makes them seeing only their personal data in both.

- Any supportive calculated columns don't work (USERNAME() function can't be used, calculate functions with filtering returns the result without the RLS context applied),

- Measures for filtering the Personal report on the visual level are not an option (I need to use cards and it's not possible to filter them by measures),

- I tried mixed mode, but this doesn't work as well,

I think the most promising option would be a calculated column, indicating who is the top-level manager in the Contact table, but within the current context of RLS. Then this column could be added to the report-level filtering in the Personal report.

I also thought about putting some IF function inside the RLS statement, but I have no idea how to differentiate the logic according to the report that is being used.

I just want to avoid having two identical datasets just with different RLS statements. Does anybody have an idea? 🙂

BR, Piotr

[PiotrStawicki · Accepted solution]

Hi, I managed to find a solution:

I created two additional, supportive "Contact" queries in the Query Editor - first has only the contact ID and username, second has ID, username and columns with 1st level manager username, 2nd level manager username etc.

Then I added a flague, on the first supportive query set to 1, on the second - to 0.

I appended the 2nd query to the first, loaded to the datamodel and created a many-to-many connection between the supportive Contact query and the main one (where supportive table filters the main table).

DAX in RLS statement configures the access to all the records (in the supportive query) where any of the columns contain user's username.

Then in the personal report, the flague is set to 1 (in the whole report), so only the standard [Username] column contains any data. In the team report, the flague is set to 0 and supportive Contact table contains both standard username and usernames of managers as well.

Maybe there is easier solution, but this works fine for me, so topic can be closed 🙂

BR, Piotr

[lbendlin]

"I just want to avoid having two identical datasets just with different RLS statements"

Why? What's wrong with that?

[PiotrStawicki]

Hi Ibendlin,

having two identical datasets, every single change in the query, DAX measure or in the logic in general needs to be applied twice. If the datasets are complex, that can be a significant, additional work to do. Even using some tool like ALM Toolkit, I need to remember about two deployments every time, if the refresh of one of these datasets fails, the results in both reports can be different for some time, and they should be exactly the same. I think there are at least few reasons why it's better to have 1 dataset instead of multiple, in this case I have really complex dataset with the data that is important for the whole organisation, so one dataset should be really better idea 🙂 

Anyway, I found a solution.