DAX Pathcontains with RLS

[Lestify]

Hello everyone,

I would like to get any suggestions on how i could use the pathcontains for dax in RLS.

example table would be a hierarchy of managers with their emails on different columns. From what I know, i need to combine all managers but what special character should i use on distributing them? like (example@email.com,example@email.com).. putting comma? or something. 

Here is like a table that i have

manager 1	manager 2	manager 3	manager 4	hierarchy
email	email	email	email	combine all managers?

how should i use pathcontains in rls? should i use username()? 

i'm new to the function. hope i can get your help guys! 
Thanks in advance. would love to discuss more.

[123abc · Accepted solution]

To use the PATHCONTAINS function in DAX for Row-Level Security (RLS) with a hierarchy of managers and their emails, you can follow these steps:

1. Combine Manager Emails:

   First, you need to combine all manager emails into a single column or string. You can use the CONCATENATEX function to achieve this. Assuming you have a table named "Managers" and columns manager1, manager2, manager3, and manager4, you can create a calculated column like this:

CombinedManagers = CONCATENATEX('Managers', 'Managers'[manager 1], ",") & "," & CONCATENATEX('Managers', 'Managers'[manager 2], ",") & "," & CONCATENATEX('Managers', 'Managers'[manager 3], ",") & "," & CONCATENATEX('Managers', 'Managers'[manager 4], ",")

1. This will create a single column with all manager emails separated by commas.

2. Define Row-Level Security Rule:

   Next, create a Row-Level Security rule using the PATHCONTAINS function. In your case, if you want to restrict access to data based on the user's email (presumably extracted from USERNAME()), you can use the following DAX expression for your RLS rule:

PATHCONTAINS(Managers[CombinedManagers], USERNAME())

1. This rule will check if the email in USERNAME() is present in the CombinedManagers column for each row. If it is, the user will have access to that row; otherwise, they won't.

2. Publish and Test:

   After defining the RLS rule, publish your Power BI report. Make sure that the USERNAME() function returns the user's email address.

Now, when users access your report, their access will be filtered based on whether their email is part of the combined manager emails.

Please note that this approach assumes that the USERNAME() function returns the user's email, and you should adapt it to your specific scenario if the actual function to get the user's email is different. Also, ensure that your data model and relationships are set up correctly.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Lestify]

Hello 123abc,

I'll test this out and will update to you as soon as possible. Thank you for this!

[123abc]

You are always wellcome dear.