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.

lbendlin's avatar
lbendlin
Super User
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()

Anonymous's avatar
Anonymous
Not applicable
6 years ago

There is no emailid field in accounta table, that is soo simple RLS, that doesn't work here.

 

we need to virtually filter the accounts table data based on user login. User email and his accounts are in table 2. 

kindly give me a solution. 

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    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

      Thank you Marcus, well in my scenario, i have already used apply security check box to another, so i cant use it here. Give me sometime, i will test again and i will post my data model picture. 

    • 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) ?