dynamic rls implemented on tabular model

[Anonymous]

hello guys,

I need to implement RLS on level of tabular model (SSAS). I have prepered security table with below structure:

• user name
• country 
• division
• subdivision

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?

[v-juanli-msft]

Hi @Anonymous 

Do you import data into Power BI or use live connection?

Best Regards

Maggie

[Anonymous]

---

@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

[v-juanli-msft]

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