Dynamic RLS

[Anonymous]

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                        J@gmail.com

1                       B@gmail.com

2                       c@gmail.com

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

Kindly help with your ideas. @PowerB @Bthurlings @dm-p @nandukrishnavs @MattCalderwood @Greg_Deckler 

[lbendlin · Accepted solution]

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()

[harshnathani]

Hi @Anonymous ,

See if these help.

https://radacad.com/what-do-you-need-to-implement-dynamic-row-level-security-in-power-bi

https://radacad.com/secure-the-sensitive-data-in-power-bi-data-masking-better-with-row-level-security

https://radacad.com/show-the-information-but-not-the-details-power-bi-data-masking

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Hi Harsh, i tried thise radcad blogs, my scenario is not suited however as you see above measures with filter condition, i tried similar to those blogs. 

[lbendlin · Accepted solution]

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]

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 · Accepted solution]

Hi @Anonymous ,

it is as easy as described by @lbendlin 

You can find my example PBIX attached.

[Anonymous]

@mwegener  
here is my model.

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

[mwegener]

Hi @Anonymous ,

doesn't it work without the checkbox?

[Anonymous]

@mwegener @harshnathani @lbendlin @sturlaws 
thank you all for your time on this. 
this might work for now.

now we have aroung 100 K records in account table.

each account might be shared with multiple users.

for instance, if one account is shared by 10 users it means 10 rows.

likewise if we have 100 records vs 10 users it will be 100 * 10.

future maintanance of the dataset will be nightmare, what should be solution to handle this ? at the same time need to use the same RLS functionality.

looking forward to hear from you.

[mwegener]

Hi @Anonymous ,

you could group the users and accounts into profiles, like in this blog post.

https://radacad.com/what-do-you-need-to-implement-dynamic-row-level-security-in-power-bi

[Anonymous]

@mwegener 
@lbendlin  thank yuo so much, with out checkbox it did worked i am validating the data.

i didn't think that this is so easy, all day writing complex DAX. fingers crossed and hope this should work.
i will let you know by EOD the update.

[Anonymous]

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

[Anonymous]

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.