RLS to filter data in a table from a set of data on another table

[HariniParthiban]

Hi,

I have to filter the report based on the branches the user has access to. I have a table called BRANCHESLIST which carries the information of branch numbers and user emails.

ASSETSETUPVIEW is the table I want to filter. Using filter directly on RLS throws an error:

I created a measure 

FilteredTable =

SUMMARIZE(

    FILTER(

        BRANCHESLIST,

        BRANCHESLIST[EMAIL_FW] = USERPRINCIPALNAME()

    ),

    BRANCHESLIST[COSTCENTRE_FW]

)

and then used RLS which gives the following error:

How can I filter the table in this scenario?

[v-yangliu-msft · Accepted solution]

Hi  @HariniParthiban ,

You can place the following code:

var UPN=USERPRINCIPALNAME()
var UserRow=
    CALCULATETABLE(
        'BRANCHESLIST',
        'BRANCHESLIST'[EMAAIL_FW]=UPN)
var Flag=
SELECTCOLUMNS(
    UserRow,'BRANCHESLIST'[COSTCENTER])
var Result=
FILTER(
'ASSETSETUPVIEW','ASSETSETUPVIEW'[COSTTCENTRE_FW] in Flag)
RETURN
IF(
    'ASSETSETUPVIEW'[COSTTCENTRE_FW] in Flag,TRUE(),FALSE())

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.