Dynamic RLS - use DAX to return multiple values IN

[Anonymous]

Hi all, 

I have a 'Userlist' table where each user (email) be connected to one or more regions, like this: 

Next, I have a region dim table connected to all relevant fact tables. 

I would like to perform a lookup to limit each user to see only the regions they are connected to, but I don't know how to do this in DAX (after several failed attempts, I am only able to return one value using lookup - not a list of values). An example of what I am trying to do, with SQL syntax, below: 

Is it possible to write this lookup inside an 'IN' function using DAX? Am I going about this the wrong way? 

[Anonymous · Accepted solution]

Hi @Anonymous,

You can try to use below formula as filter condition:

[Region]
    IN CALCULATETABLE (
        VALUES ( UserList[Region] ),
        FILTER (
            ALL ( UserList ),
            [User email] = USERNAME ()
                || [User email] = USERPRINCIPALNAME ()
        )
    )

BTW, I don't think you can directly use t-sql query in dax formula.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin, 

This worked perfectly! Thank you! 

A great solution to managing user roles using an external user-table managed by the business. 

And -- you are of course right, t-sql doesn't work - I wrote it to provide an example of what I was trying to attempt in DAX. 

Regards,

Oklande