Forum Discussion
Modify filters based on Audience or Row-Level Security
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 [email protected] Department 1 [email protected] Department 2 [email protected] Department 3 [email protected] Department 2 [email protected] Department 3
[email protected] 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 DataHi 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 [email protected] Dept-1 [email protected] Dept-2 [email protected] Dept-3 [email protected] ALL [email protected] Dept-1 [email protected] Dept-1 [email protected] 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
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 |
| [email protected] | Dept-1 |
| [email protected] | Dept-2 |
| [email protected] | Dept-3 |
| [email protected] | ALL |
| [email protected] | Dept-1 |
| [email protected] | Dept-1 |
| [email protected] | 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
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
