Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
61 | |
50 | |
45 | |
20 | |
17 |