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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
andrewnzinuk
Frequent Visitor

Row Level Security and Roles

I have a table called Cost_Centre, within that table I have a column called UPN which is the UPN of the cost_Centre manager. This column can have multiple values in it. For example I have placed the following in the UPN colum :

 

james@abc.com

mary@abc.com

Cindy@abc.com; Mark@abc.com

harry@abc.com

 

If I login as Mark@abc.com - I want to only see rows that Mark can see . I have created a role and added a filter on the UPN column - and was hoping to restrict rows based on the UPN value in the column.

 

This works when I only have a single user in the UPN column - but sometimes I need more than one person :

 

When logged in as Mark this works with Mark@abc.com as the only value in the UPN column for the row :

 

=dim_CostCentre[UPN] = USERPRINCIPALNAME()

 

So I have tried this with the string with multiple users  - and cannot make it work :

 

IF(CONTAINS('dim_CostCentre',dim_CostCentre[UPN],USERPRINCIPALNAME())

 

When logged in as Mark @abc.com no rows are visible. I think the contains is working on the column and not the row. Any ideas on how I can make this work.

 

Thanks

 

 

 

 

 

3 REPLIES 3
ppm1
Solution Sage
Solution Sage

Try this instead for your RLS filter. If performance poor, you could alway split your text into a list with Text.Split and expand that list to new rows and use your simpler filter expression.

 

CONTAINSSTRING(dim_CostCentre[UPN], USERPRINCIPALNAME() )

 

Pat

Microsoft Employee

Hi Pat

 

Went back to CONTAINSSTRING and worked perfectly as far as I have tested it.

 

My issues was I was running on Analysis Services 2017 - upgraded to 2022 and it worked.

 

I think 2019 would work as well.

 

Cheers

Hi Pat

 

Tried that - unfortunately my Visual Studio does not recognise that DAX function. I am not sure if it relates to my version of Analysis Services - which is SQL 2017. I was going to upgrade and see if CONTAINSSTRING will be accepted, is it a relatively new function . Thanks

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.