Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
MahammadJafar27
Frequent Visitor

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

RLS table --

 

MahammadJafar27_1-1707910075400.png

 

Line 1, the user a@abc.com has access to region Canada, Country Canada, Category Bikes and for All the Products.

Line 2, the user a@abc.com has access to region US, for All the countries, Categories and Products.

 

Fact table --

 

MahammadJafar27_2-1707910134305.png

 

Output --

 

MahammadJafar27_3-1707910180570.png

 

I want to implement the RLS based on the above scenario. Is it something feasible?

If yes, please guide me on how to implement this scenario.

 

Your inputs will be highly appreciated 🙂

 

Thanks

1 ACCEPTED SOLUTION

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

vxinruzhumsft_0-1708567624496.png

 

Then test it.

vxinruzhumsft_1-1708567665534.png

 

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.

View solution in original post

6 REPLIES 6
_AAndrade
Super User
Super User

Send me your Pbix file and I will try to do it.





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




_AAndrade
Super User
Super User

Hi,

I recently had to solve something similar and the video below helped me.


https://www.youtube.com/watch?v=ddqQT9Mjps4&list=PLoX1_r856EMu3xgjFbfyPiNWNX9WrU3nv&index=8&t=2504s


You should also read Reza blog. He has a lot of information about this topic and he goes from simple example to complex cases.
https://radacad.com/dynamic-row-level-security-with-power-bi-made-simple







Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




Hi @_AAndrade ,

 

Thanks for the above links, they are very informative, but I am still unable to achieve the output.

 

Could you please try the above example and implement the RLS? Let me know if you are able to achieve the desired output.

 

Your help will be highly appreciated 🙂

 

Thanks

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])

vxinruzhumsft_0-1708321788619.png

 

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] ) )
        )
    )
)

 

vxinruzhumsft_1-1708321992990.png

 

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

[User] = USERPRINCIPALNAME()

 

vxinruzhumsft_2-1708322043270.png

 

5.Test it .

vxinruzhumsft_3-1708322069445.png

 

Output

vxinruzhumsft_4-1708322080308.png

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.

 

Hi @v-xinruzhu-msft 

 

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 😊

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

vxinruzhumsft_0-1708567624496.png

 

Then test it.

vxinruzhumsft_1-1708567665534.png

 

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.