solution on restricting single country users while dynamic RLS is applied on country table

[Anonymous]

My scenario needs to implement organizational hierarchy level access in premium capacity.

I have Test database in Vertica DB where 9 levels of managers hierarchy table been pulled into a direct query.

I need globally all sales rep to need to see their country data except managers need to see all.

Country[CountryKey] = LOOKUPVALUE( its not working in DQ)

           'Security'[CountryKey],

           'Security'[EmailAddress],USERPRINCIPALNAME(),

                   'Security'[CountryKey],Country[CountryKey]

         )

Suddenly ex -USA users to be blocked, again do I need to create role -Country=”USA” and assigning the set of reps to it will it work? Bcoz these reps also part of above dynamic filter by country. In this Case, can I go country wise distribution list and remove USA Distribution list.

I have seen scenarios implemented with path /path contains /lookup values in import method but not same will work on DQ though I enabled allow restricted measures lookup value doesn’t work is there any alternative how to make it work in DQ mode.

Waiting for valuable solution

[v-juanli-msft]

Hi @Anonymous

For you scenario, you could try "IF", "SWITCH", "RELATED" function in direct query mode.

we can use the function "LOOKUPVALUE" to create measures after going to File -> Options and settings -> Options - > DirectQuery and enable the option “Allow unrestricted measures in Direct Query Mode”.

Best Regards

Maggie