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.   F...
  • 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()

  • mwegener's avatar
    mwegener
    6 years ago

    Hi Anonymous ,

     

    it is as easy as described by lbendlin 

    You can find my example PBIX attached.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

mwegener  
here is my model.

and this is the error i am getting if i try to apply security in both directions.

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

sturlaws 
yeah this is what something i am expecting.

VAR _account_id =
    CALCULATETABLE (
        VALUES ( 'table1'[account_d] ),
        FILTER ( 'table2','table2'[email] = USERPRINCIPALNAME () )
    )
RETURN
    'Table1'[account_id] IN _account_id

where whould i use this DAX to filter? in table 1(fact) or table2(user table) ?