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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Row Level security in AS Tabular

Dear All,

 

I'm trying to implement row level security on an AS Tabular Cube. It works for now, but not the way I would like. Users have access to profit centres, they are only allowed to see the profit centres they have authorisation for.

 

I have the following tables:

 

Fact Postings

- dimProfitCentreKey

-(other dimensions)

-(measures)

 

Dim ProfitCentre

-dimProfitCentreKey

 

Fact DLS Profit Centre

-dimProfitCentreKey

-dimUserKey

-(useraccount) * added to get this to work, doesn't belong in a fact.

 

DimUser

-dimUserKey

-useraccount

 

I use the following code as DAX filer on the dimProfitCentre.  It works because I added useraccount as a field to the fact DLS Profit Centre, it doesn't make use of the DimUser, as I would like. All the examples I've found are based on one table, not two.

 

='Profit Centre'[dim_profit_centre_key]=LOOKUPVALUE('DLS Profit Centre'[dim_profit_centre_key],'DLS Profit Centre'[useraccount],USERNAME(),'DLS Profit Centre'[dim_profit_centre_key],'Profit Centre'[dim_profit_centre_key])

 

How should I do this when I want to use the useraccount field in the DimUser to authorise the DimProfitCentre?

 

Regards,

Jeroen

 

 

1 REPLY 1
Stachu
Community Champion
Community Champion

Hi Jeroen

you can use RELATED to avoid adding a user key to the fact table

so for the 'Fact DLS Profit Centre' this should work 

RELATED('DimUser'[useraccount]) = USERPRINCIPALNAME()

for 'Dim Profit Centre'

VAR __ProfitCentresFiltered = FILTER('Fact DLS Profit Centre', RELATED(DimUser[useraccount] )= USERPRINCIPALNAME())
VAR __OnlyProfitCentres = SUMMARIZE(__ProfitCentresFiltered, 'Fact DLS Profit Centre'[dimProfitCentreKey])
RETURN
'ProfitCentre'[dimProfitCentreKey] IN __OnlyProfitCentres
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.