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.
hello guys,
I need to implement RLS on level of tabular model (SSAS). I have prepered security table with below structure:
all columns has corresponding ones in lookup table with accessed data
now, I need to implement in DAX following logic:
- if the user has filled only country column, then he should has access to all division&subdivision data for this particular country
- if the user has filled country and division column, then he should has access to all data for particular country and division in all subdivisions
- if the user has filled country, division and subdivision, then he should has access only to this paricular data set
moreover, one user can have many records in security table
any ideas?
Hi @Anonymous
Do you import data into Power BI or use live connection?
Best Regards
Maggie
@v-juanli-msft wrote:Do you import data into Power BI or use live connection?
Yes, I am using live connection to tabular model defined by Analysis Services
@v-juanli-msft wrote:Does one user name has many different countries rows?
Yes, it can be like that. Users defined in dim Security should have access to dim Employee data for particular values of attributes of country, division and subdivision.
I have prepared below DAX code, which I am planning to implement on Employee table as row filter:
= ( (NOT(ISBLANK(Employee[EmployeeCountryCode]) || Employee[EmployeeCountryCode] = "") && Employee[EmployeeCountryCode] = LOOKUPVALUE ( dimSecurity[CountryCode], dimSecurity[DomainLogin], USERNAME (), dimSecurity[CountryCode], Employee[EmployeeCountryCode] ) ) || (NOT(ISBLANK(Employee[EmployeeDivisionCode]) || Employee[EmployeeDivisionCode] = "") && Employee[EmployeeDivisionCode] = LOOKUPVALUE ( dimSecurity[DivisionCode], dimSecurity[DomainLogin], USERNAME (), dimSecurity[DivisionCode], Employee[EmployeeDivisionCode] ) ) || (NOT(ISBLANK(Employee[EmployeeSubdivisionCode]) || Employee[EmployeeSubdivisionCode] = "") && Employee[EmployeeSubdivisionCode] = LOOKUPVALUE ( dimSecurity[SubdivisionCode], dimSecurity[DomainLogin], USERNAME (), dimSecurity[SubdivisionCode], Employee[EmployeeSubdivisionCode] ) ) )
dimSecurity table is unconnected, what do you think? maybe you have a better solution?
Regards,
Jacob
Hi @Anonymous
Does one user name has many different countries rows?
For example
user name | country | division | subdivision |
a | aa | aaa | aaaa |
a | bb | bbb | bbbb |
Please show me an example of the relationship of each columns.
one to many?
many to many?
many to one?
Best Regards
Maggie
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |