RLS based on CONTAINSSTRING?

[Ben1981]

Hi,

Is there a way with RLS to filter a column based on if a value is contained within a string? 

I have two tables setup, HR People and HR Hierarchy, no connected to each other but can be via Person ID.

The HR people table has all employee IDs, Name and Email address and then the Hierarchy has PersonID and Path (path being a string that contains all the person IDs above that person, seperated by a deilimiter)

I have tried this bit of code but so far it has not worked as I hoped..

VAR
GetUserID =
MAXX(
FILTER('Dim HR People',
'Dim HR People'[Email] = USERPRINCIPALNAME() ),
'Dim HR People'[ID Key] )
RETURN
[Path] = CONTAINSSTRING('_Dim Hierarchy'[Path],GetUserID)

Anyone help would be appreiciated.

[v-pnaroju-msft]

Hi Ben1981,

We kindly request you to provide sample data that clearly demonstrates your issue or query in a structured format (not as an image) to help us understand and resolve the matter. Please ensure that the data is relevant, free from any sensitive information, and directly related to the issue. Additionally, please share the expected outcome based on the given example.

Thank you.

[v-pnaroju-msft]

Hi Ben1981,

Please share some sample data that shows your problem or question clearly in a simple and organized way (not as an image). This will help us understand and solve the issue better. Make sure the data is relevant, does not have any sensitive information, and is related to your problem. Also, please tell us what result you expect from this example.

Thank you.

[v-pnaroju-msft]

Thankyou, @Ahmedx and @pankajnamekar25 for your response.

Hi Ben1981,

We appreciate your question on the Microsoft Fabric Community Forum.

We kindly request you to provide sample data that clearly demonstrates your issue or query in a structured format (not as an image) to help us understand and resolve the matter. Please ensure that the data is relevant, free from any sensitive information, and directly related to the issue. Additionally, please share the expected outcome based on the given example.

Thank you.

[pankajnamekar25 · Accepted solution]

Hello @Ben1981 

try with this DAX code

In your RLS role definition (for table 'Dim HR Hierarchy')

VAR _UserID =
MAXX(
FILTER('Dim HR People', 'Dim HR People'[Email] = USERPRINCIPALNAME()),
'Dim HR People'[ID Key]
)
RETURN
CONTAINSSTRING('Dim HR Hierarchy'[Path], _UserID)

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.


Thanks,


Connect with me on:

LinkedIn

[Ben1981]

Hi, so this seems to work when I view role as me but if I try view as other and put in the login details for another user then it doens't work. 

So not sure if it's because of the view as other isn't working right because of the details I've inputted or if the code above does not work?

[Ahmedx]

study this

VAR
GetUserID =
MAXX(
FILTER('Dim HR People',
'Dim HR People'[Email] = USERPRINCIPALNAME() ),
'Dim HR People'[ID Key] )
RETURN

CONTAINS('_Dim Hierarchy,'_Dim Hierarchy'[Path],GetUserID)

https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi