Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

dynamic rls implemented on tabular model

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?

Need Help

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous 

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

     

    Best Regards

    Maggie

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago



      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