Dynamic RLS - USERPRINCIPALNAME returning more than 1 criterium

[Nico_Beckers]

Hello, 

I'm having difficulties adjusting my current RLS statement:

OR(PATHCONTAINS('DIM-Organogram'[PathRLS], MAXX(FILTER('DIM-Organogram', [Organogram.Email] = USERPRINCIPALNAME()), 'DIM-Organogram'[Supervisor])),PATHCONTAINS('DIM-Organogram'[PathRLS], MAXX(FILTER('DIM-Organogram', [Organogram.Email] = USERPRINCIPALNAME()), 'DIM-Organogram'[FunctionRRnr])))

The current statement will check what supervisor function or functionRRnr is linked to USERPRINCIPALNAME of the viewer and will then check in the path column of the dataset for corresponding values, which will then be shown.

This works perfectly if the USERPRINCIPALNAME only matches with 1 distinct value in the supervisor function column.

However, if multiple values occur for the same USERPRINCIPALNAME, I will only see 1 filtered due to the MAXX function.

Is there a way to make it work also in cases where there is more than 1 supervisor function?

Many thanks in advance.

Kr,

Nico

[kushanNa · Accepted solution]

hi , you should create a users and users and their variables (Supervisor and Functions) table for this , this variable table should include two column users & all the variables related to that user

and then create a relationship between user and variable table

And create this measure

SelectedValuesFilter3 =
VAR SelectedValues = VALUES('User&Sup&Fun'[Value])  
VAR MatchFound =  
    SUMX(  
        SelectedValues,  
        IF(  
            CONTAINSSTRING( MAX(Table2[PathF]),'User&Sup&Fun'[Value] ),  
            1,  
            0  
        )  
    )  
RETURN  
IF(MatchFound > 0, 1, 0)

I have create a new path column called pathf in here without the first part of the path but if you need it as well you can use the orginal path & Table2 include your main table name in there

And include this measure into your table filter and say you only need to see 1

And apply RLS to users table and it should work

[kushanNa]

Hi,

I would suggest that you create a users table for this and apply RLS to both 

the test i did 

RLS dax codes 

OR(
    CONTAINSSTRING(
        'DIM-Organogram'[PathRLS], 
        MAXX(
            FILTER('Users', 'Users'[Organogram.Email] = USERPRINCIPALNAME()), 
            'DIM-Organogram'[Supervisor]
        )
    ),
    CONTAINSSTRING(
        'DIM-Organogram'[PathRLS], 
        MAXX(
            FILTER('Users', 'Users'[Organogram.Email] = USERPRINCIPALNAME()), 
            'DIM-Organogram'[FunctionRRnr]
        )
    )
)

[Organogram.Email] ==USERPRINCIPALNAME()

before view as

after view as eve@company.com 

[Nico_Beckers]

Hello kushanNa

This solution will filter my dataset starting with the email address of the Userprincipalname. So only the lines containing that email address will be visible right?

I need my RLS to check the content related to the user email address; the info in the supervisor column, and in the FunctionRRnr column, take that information and check with Pathcontains if that info is in the Path. So the content of Organogram.email should not be filtered.

So Eve@company.com has 2 rows in the dataset. Supervisor column contains A and B, FunctionRRnR  column contains C and D. Now I want my RLS to take A, B, C and D and check where any of those is present in my Path and only show the rows where the Path either contains A, B, C or D

[kushanNa]

is it possible for you to create a sample table and an expected output table? 

[Nico_Beckers]

So if I login as A@company.com I want the yellow cells to be used as criteria for filtering the PathRLS column. Only show if it contains at least one of those criteria.

Outcome: 

Dataset:

Organogram.email	PathRLS	Supervisor	FunctionRRnr
A@company.com	SSPV1|Function1	Spv1	Function1
A@Company.com	SSPV2|Function2	Spv2	Function2
B@Company.com	SSPV1|Spv2|Function3	Emp1	Function3
C@Company.com	SSPV1|Spv2|Function3	Emp2	Function3
D@Company.com	SSPV1|Spv1|Function5	Emp3	Function5
E@Company.com	SSPV3|Spv3|Function6	Spv3	Function6
F@Company.com	SSPV3|Spv1|Function5	Emp4	Function5
G@Company.com	SSPV2|Spv3|Function3	Emp5	Function3

[prasannagoloju]

A better approach is to use CONCATENATEX instead of MAXX, ensuring all relevant values are captured. Modify your RLS rule by using CONCATENATEX to combine multiple Supervisor or FunctionRRnr values into a single string separated by "|". Then, PATHCONTAINS can check against multiple values instead of just one. This ensures that all relevant data is included rather than just the maximum value. Let me know if you need further refinements or debugging help.

OR(
PATHCONTAINS('DIM-Organogram'[PathRLS],
CONCATENATEX(FILTER('DIM-Organogram', [Organogram.Email] = USERPRINCIPALNAME()), 'DIM-Organogram'[Supervisor], "|")
),
PATHCONTAINS('DIM-Organogram'[PathRLS],
CONCATENATEX(FILTER('DIM-Organogram', [Organogram.Email] = USERPRINCIPALNAME()), 'DIM-Organogram'[FunctionRRnr], "|")
)
)

[Nico_Beckers]

Hi prasannagoloju, 

This expression filters everything out of the dataset. If I have 2 values, A and B, wont it look for A|B then in pathcontains with this formula?

Kr, 

Nico