Forum Discussion

Nico_Beckers's avatar
Nico_Beckers
Frequent Visitor
1 year ago
Solved

Dynamic RLS - USERPRINCIPALNAME returning more than 1 criterium

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

Data Protection
Need Help
  • kushanNa's avatar
    kushanNa
    1 year ago

    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

     

     

     

     

     

     

     

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Nico_Beckers
      Frequent Visitor
      1 year ago

      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

  • kushanNa's avatar
    kushanNa
    Super User
    1 year ago

    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 [email protected] 

     

     

    • Nico_Beckers's avatar
      Nico_Beckers
      Frequent Visitor
      1 year ago

      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 [email protected] 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's avatar
        kushanNa
        Super User
        1 year ago

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

  • kushanNa's avatar
    kushanNa
    Super User
    1 year ago

    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