Forum Discussion

MahammadJafar27's avatar
MahammadJafar27
Advocate I
2 years ago
Solved

Need help to implement the Dynamic RLS based on the below scenario.

RLS table --     Line 1, the user [email protected] has access to region Canada, Country Canada, Category Bikes and for All the Products. Line 2, the user [email protected] has access to region US, for All...
Data Protection
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi MahammadJafar27 

    Try the following measure, remove the rule in user table, and create a new rule in fact table.

    COUNTROWS (
        FILTER (
            RLS,[User] = USERPRINCIPALNAME()&&
            CONTAINSSTRING ( [Region_Type], EARLIER ( 'Fact'[Region] ) )
                && CONTAINSSTRING ( [Category_type], EARLIER ( 'Fact'[Category] ) )
                && CONTAINSSTRING ( [Country_type], EARLIER ( 'Fact'[Country] ) )
                && CONTAINSSTRING ( [Pro_Type], EARLIER ( 'Fact'[Product] ) )
        )
    )=1

     

    Then test it.

     

    Best Regards!

    Yolo Zhu

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi MahammadJafar27 

You can try the following solution.

create four calculated columns in RLS table.

Region_Type = IF([Region]="All",CONCATENATEX(DISTINCT('Fact'[Region]),[Region],","),RLS[Region])
Country_type = IF([Country]="All",CONCATENATEX(DISTINCT('Fact'[Country]),[Country],","),[Country])
Category_type = IF([Category]="All",CONCATENATEX(DISTINCT('Fact'[Category]),[Category],","),[Category])
Pro_Type = if([Product]="All",CONCATENATEX(DISTINCT('Fact'[Product]),[Product],","),[Product])

2.Create a table and set it cannot be viewed .

Table = SELECTCOLUMNS(RLS,"a",[User])

 

3.Create a measure and put it to the table visual.

MEASURE =
IF (
    COUNTROWS ( RLS ) = COUNTROWS ( 'Table' ),
    1,
    COUNTROWS (
        FILTER (
            RLS,
            CONTAINSSTRING ( [Region_Type], MAX ( 'Fact'[Region] ) )
                && CONTAINSSTRING ( [Category_type], MAX ( 'Fact'[Category] ) )
                && CONTAINSSTRING ( [Country_type], MAX ( 'Fact'[Country] ) )
                && CONTAINSSTRING ( [Pro_Type], MAX ( 'Fact'[Product] ) )
        )
    )
)

 

 

4.Create a RLS named test and input the following code in RLS Table.

[User] = USERPRINCIPALNAME()

 

 

5.Test it .

 

Output

Best Regards!

Yolo Zhu

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

 

MahammadJafar27's avatar
MahammadJafar27
Advocate I
2 years ago

Hi Anonymous 

 

Thanks a lot, you are amazing!!!

 

The solution satisfies the above requirement.

 

I have few more challenges.

  1. If I have multiple visuals, I need to apply 'MEASURE >0' for each visual. What if the user has access to personalize visual?
  2. I am trying to apply the same logic in fact table (Modelling -->Test --> Fact) so that I can filter the fact table but couldn’t be able to achieve it. Is that something achievable? Please let me know.

Your help will be highly appreciated 😊

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi MahammadJafar27 

    Try the following measure, remove the rule in user table, and create a new rule in fact table.

    COUNTROWS (
        FILTER (
            RLS,[User] = USERPRINCIPALNAME()&&
            CONTAINSSTRING ( [Region_Type], EARLIER ( 'Fact'[Region] ) )
                && CONTAINSSTRING ( [Category_type], EARLIER ( 'Fact'[Category] ) )
                && CONTAINSSTRING ( [Country_type], EARLIER ( 'Fact'[Country] ) )
                && CONTAINSSTRING ( [Pro_Type], EARLIER ( 'Fact'[Product] ) )
        )
    )=1

     

    Then test it.

     

    Best Regards!

    Yolo Zhu

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