Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic RLS

Hi,

Say like 2 tables.

1 is fact with unique accounts and its transactions.

2 is accountid and email ids.

 

an account can be shared with multiple users, so table 2 will have many records.

 

For an instance, 

Table 1:

Account ID   

1

2

3

 

Table 2:

Accountid         EmailId

1                        [email protected]

1                       [email protected]

2                       [email protected]

 

Like this.

 

so now i want to fapply RLS based on user login.

 

If J login he should see his accounts, if B login he should see his accounts etc..

 

i tried few measures like this but not working.

ConcatenateLookupEmail =
DISTINCT(SELECTCOLUMNS(
FILTER(Table,
Table1[AccountId] =
LOOKUPVALUE(
'Table2'[Accountid],
'Tbale2'[Email],
USERPRINCIPALNAME()
)
),
"Accountid",
Table1[AccountId])
)
CalTable = CALCULATETABLE(
VALUES(Table1[AccountId]),
'Table2'[Email] = [uname],
CROSSFILTER(Table1[AccountId],'Tbale2'[Accountid],Both))
 
 
  • lbendlin's avatar
    lbendlin
    6 years ago

    Let the data model do the work.  Link the tables, make sure the filter direction is pointing from accounts to transactions, and put the RLS on the accounts table with a simple formula:

     

    [Emailid]=USERPRINCIPALNAME()

12 Replies