Modify filters based on Audience or Row-Level Security

[ianxoca]

Hi, all, 

I have a dashboard where I need to modify a filter (Department) based on the current user. Each user should only see data of their own department. For this I could use RLS. 

The problem comes when certain users need to see more than one department or even all of them.

Let's say I have five departments.... even though most users will only need to see their own, there are a few who will need access to departments 1 and 4, some to 2 and 3, some to all 5, etc... 

For now, I created (duplicated) dashboards for each department, set the filter accordingly and added them to an app. Now the app contains multiple similar-looking dashboards, each filtered differently... then I'm using audiences to make one, some, or all dashboards available to the user.

The main problem with this is that the dashboards are very hard to maintain, and any changes need to be replicated on each dashboard before updating the app, which is simply not ideal. 

• Is there a way to use audiences to modify filters on a single dashboard?
• A way to modify multiple dashboards using a "Master Dashboard"?
• Can I use RLS and then override it for some users by using audiences?

Looking for advice! Has anyone dealt with a similar use case and do you have any suggestions?

Thank you. 

[Jonvoge · Accepted solution]

Hi ianxoca

You can actually solve this in one single report, just by using RLS and a user table.

You will need to construct a User Table which defines what each User is allowed to see. An example:

Email	Department
User1@mail.com	Department 1
User1@mail.com	Department 2
User1@mail.com	Department 3
User2@mail.com	Department 2
User3@mail.com	Department 3
User4@mail.com	Department 4

Notice how User 1 has multiple rows in this table? This is what allows that user to see the data for Department 1, 2 and 3. The other Users can only see their own department.

After creating this User Table, you need to create a relationship between this User Table and your existing data model, by using the Department column.

Finally, you need to create an RLS role, using dynamic RLS to filter the User Table.

Basically, you create a role, and add the following DAX code to the UserTable in the role: Email = USERPRINCIPALNAME()

Finally, publish the report and add all users to the role you created to apply RLS.

The Dynamic RLS ensures that when a user logs in, the User Table is filtered to only show rows that matches the logged in users email. More details on Dynamic RLS can be found here: Dynamic Row Level Security with Power BI Made Simple - RADACAD

_____________________________________________________
I hope my comment was helpful.
If your question was answered, please mark your post as 'Solved' and consider giving me a 'Thumbs Up'.
Find me on LinkedIn, Sessionize, or my blog Downhill Data

[pallavi_r · Accepted solution]

Hi @ianxoca ,

This is my favourite question 🙂 as I have resolved this in past. Here it is.

Step 1 - Build a useraccess table for RLS as shown below. And do not build any relationship modelling with other facts or dimension table as we have a scenario of 'ALL' department access. Everything will be handled using DAX measure. In the below table, user one is given access to all the department by adding each depatment id for the user. User 2 is given access to all 3 departments by giving 'ALL'. Both will work. User 3 and User 4 have got access to selective departments.

User_Email_ID	Department_ID
user1@xyz.com	Dept-1
user1@xyz.com	Dept-2
user1@xyz.com	Dept-3
user2@xyz.com	ALL
user3@xyz.com	Dept-1
user4@xyz.com	Dept-1
user4@xyz.com	Dept-2

Step 2 - Once the UserAccess table is built go to Modelling-> Manage Role. Now select the Table where department id is present and it needs to be filtered.

Step 3- Write the below table filter dax expression

VAR Dept_Sel =
CALCULATETABLE (
VALUES ( 'UserAccess'[Department_ID] ),
'UserAccess'[User_Email_ID]= USERPRINCIPALNAME () )
RETURN
OR (
"ALL" IN Dept_Sel,
'Department'[Department_ID] IN Dept_Sel
)

Please try this solution and let me know if you have question. Happy to help. Appreciate a kudos if I have helped you in resolving this issue. 

Thanks,

Pallavi

[CEA378]

Hey Pallavi

I have a question about your method.

I have 1 app where I have approx 8 different audience. I will call them shop1, shop2, shop3 etc.. In the app I have 6 different reports which will be available for all 8 shops but the shops may of course only see their own data.

Therefore, I thought that I could use your method to filter the users from each shop with a table which include their emails as you call UserAccess.

Whe I use your method the problem is that the users cannot see any data when opening the report. Power BI says that thet do not have access to the data even though they should as I have filtered through the UserAccess table. If I insert their email in security for each semantic model then i works but this demand much manual work.

I have found other ways similiar to yours as Dynamic Row level security but in these method I have to insert each person which need access into security for each semantic model. As I have many reports and many persons which need access then this method will need a lot of manual work to maintain as many people need access.

When I read your method I thought that I could skip the part of inserting the email of each user into security for each semantic model.

Do you know if it is possible to filter based on the audience where I don't have to give each user access by inserting their email in security in Power BI services for each semantic model.

I hope that you can help.

Best regards,

Christoffer

[Jonvoge · Accepted solution]

Hi ianxoca

You can actually solve this in one single report, just by using RLS and a user table.

You will need to construct a User Table which defines what each User is allowed to see. An example:

Email	Department
User1@mail.com	Department 1
User1@mail.com	Department 2
User1@mail.com	Department 3
User2@mail.com	Department 2
User3@mail.com	Department 3
User4@mail.com	Department 4

Notice how User 1 has multiple rows in this table? This is what allows that user to see the data for Department 1, 2 and 3. The other Users can only see their own department.

After creating this User Table, you need to create a relationship between this User Table and your existing data model, by using the Department column.

Finally, you need to create an RLS role, using dynamic RLS to filter the User Table.

Basically, you create a role, and add the following DAX code to the UserTable in the role: Email = USERPRINCIPALNAME()

Finally, publish the report and add all users to the role you created to apply RLS.

The Dynamic RLS ensures that when a user logs in, the User Table is filtered to only show rows that matches the logged in users email. More details on Dynamic RLS can be found here: Dynamic Row Level Security with Power BI Made Simple - RADACAD

_____________________________________________________
I hope my comment was helpful.
If your question was answered, please mark your post as 'Solved' and consider giving me a 'Thumbs Up'.
Find me on LinkedIn, Sessionize, or my blog Downhill Data