CODEWITHSUNDEEP
sqlsql-serverssasdaxtabular
Elaine
Elaine

Reputation: 11

SSAS Tabular model - Creating a Role Using DAX Lookupvalue to lookup muliple columns

I am only starting to use DAX and Tabular modelling so would appreciate any help/advice possible.

I have created my first model, and want to create dynamic security roles. I have achieved this on a simple scale, if i want to check the security table i created against my filtered table it checks if the columns match - and return results only for those columns that match.

For example: My security table is called ReportAccessPermissions This table currently holds the domain login, emp id, sales region, different leader info aligned to that employee. It looks something like this: 

ID   Domain     Mgr1ID  Mgr2ID  MGr3ID  SalesRegion
1234 TEST\user1 2222    NULL    NULL    NULL    
1234 TEST\user1 NULL    5555    NULL    NULL    
1234 TEST\user1 NULL    NULL    NULL    APJ     
1234 TEST\user1 NULL    NULL    NULL    EMEA

When i add the filter against the EMP table - because i want to only filter and return users with same Sales region then i have been using this, i have tested this and it seems to be working well. 

='EMPLOYEE'[SalesRegion]= LOOKUPVALUE ('ReportAccessPermmisions'[SalesRegion],'ReportAccessPermmisions'[DomainLogin],USERNAME(),'ReportAccessPermmisions'[SalesRegion],'EMPLOYEE'[SalesRegion])

However, I would like to be able to also filter on the leader info. So that i can check both the sales region and the leader info against the emp table. Is it possible to do this with one DAX query lookup? Or do i need to create separate roles for each column check?

Ideally, i would prefer not to create a lot of security roles for a model, because i imagine the maintenance on that would be outrageous as the more models are created and users are added and filters are being applied. I am hoping if its possible to create one role but have it check different columns in this security table to only then return the same results against the emp table.

Thank you, P

Upvotes: 1

Views: 725

Answers (1)

GregGalloway
GregGalloway

Reputation: 11625

Try the following pattern:

=OR(
 'EMPLOYEE'[SalesRegion]= LOOKUPVALUE ('ReportAccessPermmisions'[SalesRegion],'ReportAccessPermmisions'[DomainLogin],USERNAME(),'ReportAccessPermmisions'[SalesRegion],'EMPLOYEE'[SalesRegion]),
 'EMPLOYEE'[Mgr1ID]= LOOKUPVALUE ('ReportAccessPermmisions'[Mgr1ID],'ReportAccessPermmisions'[DomainLogin],USERNAME(),'ReportAccessPermmisions'[Mgr1ID],'EMPLOYEE'[Mgr1ID])
)

Upvotes: 1

Related Questions