Hi All,
I have created a Dashboard in Power BI and importing data from Azure Synapse SQL server using custom SQL.
In the table apart from rest of the column we have added 3 Roles created which contains multiple users names in comma seperated form as per below mentioned table for each rows.
| Role 1 | Role 2 | Role 3 |
| A,B,C | D,E,F | G,H,I |
and In order to implement RLS we have used 2 measures like below :-
1) Below measure extract the exact user name so that it matches with any of the above mentioned columns.
User Access = UPPER(SUBSTITUTE(USERNAME(),"Domain\","",1))
2) And then I have used below measure .
UserExists =
VAR CurrentUser = [User Access] // Get the current user's name
RETURN
IF (
COUNTROWS (
FILTER (
Financial, // Replace with your table name
[Role 1] = CurrentUser || [Role 2] = CurrentUser || [Role 3] = CurrentUser
)
) > 0,
TRUE(),
FALSE()
)
Afte that I go to Managed Roles and created 3 roles (Role 1, Role 2, Role 3 ) there and go to Table name and used this formula :-
[UserExists]=True
After following all the above mentioned steps when I am checking RLS as view as other users than its showing me all the data.
but when I am using view as option as roles than its showing no data which is correct as my name is not in any Role.
But I am not able to understand why it's showing me all data if I am using other user and those users doesnot exist ?
This is first time I am impletment RLS , so looking forward for your valuable inputs.
