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
