Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

Dynamic RLS - use DAX to return multiple values IN

Hi all, 

 

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

Role access.PNG

 

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: 

 

RLS Dax example.PNG

 

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

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.