Forum Discussion
Problem with 2 reports connected to common dataset with 2 RLS statements
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
"I just want to avoid having two identical datasets just with different RLS statements"
Why? What's wrong with that?
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.