Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

  • Stachu's avatar
    Stachu
    Community Champion
    7 years ago

    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!