cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JWNIBC New Member
New Member

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
Highlighted
Stachu Super Contributor
Super Contributor

Re: Row Level security in AS Tabular

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!

Proud to be a Datanaut!

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 387 members 3,180 guests
Please welcome our newest community members: