Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
RLS table --
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 --
Output --
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
Solved! Go to Solution.
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.
Send me your Pbix file and I will try to do it.
Proud to be a 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
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
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.
Thanks a lot, you are amazing!!!
The solution satisfies the above requirement.
I have few more challenges.
Your help will be highly appreciated 😊
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.
User | Count |
---|---|
123 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
183 | |
92 | |
67 | |
62 | |
52 |