Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
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
Proud to be a Datanaut!
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |