Row Level security in AS Tabular

[Anonymous]

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

[Stachu]

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!