Row Level Security with multiple conditions

[veenashenolikar]

Hello,

I am facing an issue to setup RLS in my datamodel .

The scenario is as follows -

I have a security table which enlists information as - (Please note this is sample data)

Profession	Gender	Location	Email Address
All	Female	India	M
Lawyer	All	US	R
Doctor	Male	China	S
Engineer	Male	All	A
All	All	UK	N
Lawyer	All	Germany	R
Doctor	Male	AU	D
Engineer	Male	India	S
All	All	Dubai	H

The user H gets to see all professions in Male & Female residing in Dubai, however M only sees all Female Professions residing in India. 

I tried to create one RLS table and join to the different Dim - Profession , Dim - Gender & Dim - Location , but as they all are joined to my Fact, it keeps only one relationship active. 

If you could please help me out with the same.

Thanks!

[visheshjain]

Hi @veenashenolikar,

It might sound simple but I have to ask, have you tried using the USERELATIONSHIP() function in your DAX measure for RLS?

The function uses inactive relationships.

Hope this helps.

Thank you,

Vishesh Jain

[veenashenolikar]

Hi @visheshjain ,

I tried writing the DAX but was not able to form the complete expression.

If you could please help me with the same.

Thanks in advance!

[visheshjain]

Hi @veenashenolikar,

Please refer to this doc USERELATIONSHIP function (DAX) - DAX | Microsoft Learn

Also, I can give it a shot, if you can share your sample file.

One more thing, you will need to replace ALL in your Gender column with 2 rows, unless you have the value 'All' in your data as well.

Thank you,

Vishesh Jain

[veenashenolikar]

Hi @visheshjain ,

I am handling the ALL using a crossjoin with the dimesion table and a custom column.

I will send over the sample file for your perusal

Thanks,

Veena