topic Re: Dynamic RLS in DAX Commands and Tips

Dynamic RLS

Anonymous — Wed, 24 Jun 2020 18:04:28 GMT

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

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

Re: Dynamic RLS

lbendlin — Wed, 24 Jun 2020 21:53:49 GMT

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

Re: Dynamic RLS

Anonymous — Thu, 25 Jun 2020 04:07:24 GMT

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.

Re: Dynamic RLS

Anonymous — Thu, 25 Jun 2020 04:49:37 GMT

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)

Re: Dynamic RLS

Anonymous — Thu, 25 Jun 2020 06:12:32 GMT

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.

Re: Dynamic RLS

mwegener — Thu, 25 Jun 2020 06:25:43 GMT

Hi Anonymous ,

it is as easy as described by lbendlin

You can find my example PBIX attached.

Re: Dynamic RLS

Anonymous — Thu, 25 Jun 2020 06:32:08 GMT

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.

Re: Dynamic RLS

Anonymous — Thu, 25 Jun 2020 06:55:13 GMT

mwegener
here is my model.

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

Re: Dynamic RLS

Anonymous — Thu, 25 Jun 2020 06:57:37 GMT

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

Re: Dynamic RLS

mwegener — Thu, 25 Jun 2020 07:11:27 GMT

Hi Anonymous ,

doesn't it work without the checkbox?

Re: Dynamic RLS

Anonymous — Thu, 25 Jun 2020 07:37:08 GMT

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.

Re: Dynamic RLS

Anonymous — Thu, 25 Jun 2020 12:08:56 GMT

mwegener Anonymous 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.

Re: Dynamic RLS

mwegener — Thu, 25 Jun 2020 19:49:02 GMT

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