Why row level security isn't working in this report ?

[cruncher]

Hi Folks,

I am new to powerbi and having hard time in setting up row level security.

I have attached the sample workbook here as i couldn't find the option to upload the workbook.

https://file.io/n0HR4p4yunUU 

When I view as any user, data doesn't filter at all. My users list consists of 3 columns with delimited concatenated strings.

Please help. Thank you

[rajendraongole1]

Hi @cruncher - Not able to download the file, can you please upload the file in drive?

Go to Transform Data.

Select the column with concatenated roles, and use the Split Column function to split by a delimiter (like a comma or semicolon).

Expand the resulting columns to create multiple rows for each role.

In Power BI Desktop, define roles based on the user information

Go to Modeling > Manage Roles.
Create a new role, such as UserRoleFilter.
Apply a DAX filter to the User Access Table using the USERPRINCIPALNAME() function

[Username] = USERPRINCIPALNAME()

[cruncher]

Thanks for looking at it @rajendraongole1 . Please find revised link with onedrive Row Level Sec.pbix . 

This problem is different as roles are not concatenated but users are and can't split in into multiple columns

[rajendraongole1]

Hi @cruncher - Please check the attached pbix file. 

i have combined with userlist using calculated column and it is working as expected. please check

[cruncher]

Sorry I didn't clarified it before. Anton, brandy and zene are different users not a single one. Additionally all users list columns can't be combined into one single column as it will exceed the limit of character in single columns. We have to treat them as different

For example:

a. anton and brandy can see all region, Verticals and BU

b. zeptr and Zene can see all verticals and Business Units of their respective Region.

c. bicar can see only BU1 from both the Regions

d. anna can access all BU and regions but only with Vertical 1

[cruncher]

Thank you @rajendraongole1 . I was doing it in wrong way from the start. In the view by user/role window, I was only selecting Other user checkbox only. We need to select both Other User and User checkbox to validate the security. Security working fine now.

There is another problem if you can optimize it. User List columns start from 1 and end at 26. In Users List 1 column, there will all users starting with a character, In users list column2 all users starts with b character and so on and users list column 26 contains list of users starting with z username.

How can we modify the dax so that first we check the first alphabet of logged in user and then check directly in the userslist column where that users list is stored rather than searching the logged usernam in all 26 columns which will be not optimized way.

Thank you for your help so far

[rajendraongole1 · Accepted solution]

Hi @cruncher - Use the SWITCH function to dynamically check the correct column based on the extracted first letter. Here’s an example DAX measure for this logic:

UserAccessCheck =
VAR LoggedInUser = USERPRINCIPALNAME()
VAR FirstLetter = LEFT(LoggedInUser, 1)
RETURN
SWITCH(
FirstLetter,
"A", CONTAINSSTRING([Users List 1], LoggedInUser),
"B", CONTAINSSTRING([Users List 2], LoggedInUser),
"C", CONTAINSSTRING([Users List 3], LoggedInUser),
"D", CONTAINSSTRING([Users List 4], LoggedInUser),
"E", CONTAINSSTRING([Users List 5], LoggedInUser),
// Continue for each letter up to Z
"Z", CONTAINSSTRING([Users List 26], LoggedInUser),
FALSE()
)

This approach is optimized since it only evaluates a single column based on the first letter.
Ensure that your Users List columns in Power BI contain only the usernames you need, ideally as concatenated strings.

Hope this helps.