Dynamic RLS Help

[Anonymous]

Hi All,

Im an RLS noob so to speak and trying to figure things out, so I have built a Security table something like this:

Email:	Account:	Region	Country:
User1@users.com	Apple	EUROPE	FRANCE
User1@users.com	Apple	EUROPE	GERMANY
User2@users.com		EUROPE
User3@Users.com			ITALY
User4@Users.com

So I have put formula into rules that check based on a formula to against USERPRINCIPALNAME if they have a match

IE Account: 

[Account]
IN CALCULATETABLE (
VALUES ( SECURITY[Account] ),
FILTER (
ALL ( SECURITY ),
SECURITY[Email] = USERPRINCIPALNAME()
)
)

And that works... for user1 only... for users 2,3 &4 they dont have an account set and as such need to ignore that filter.

I tried adding if(isblank( to the above but either I didnt do it right or it doesnt work there.

Is this possible?

Basically I want to build in potential rules for those 3 columns and if they are NOT NULL then the rule applies if it is NULL then I want it to skip that rule.

Makes sense I hope?

Any help/suggestions MOST welcome.

Thanks,

[v-yuta-msft]

@Anonymous ,

Please try this:

[Account] =
IF (
    SECURITY[Account] <> BLANK (),
    CALCULATE (
        MAX ( SECURITY[Account] ),
        FILTER ( SECURITY, SECURITY[Email] = USERPRINCIPALNAME () )
    )
)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hey,

Sorry only getting back to this now... I modified what you suggested to fix an error about multiple values with:

[Account] =
IF (
LOOKUPVALUE(SECURITY[Account],SECURITY[Email],USERPRINCIPALNAME ()) <> BLANK (),
CALCULATE (
MAX ( SECURITY[Account] ),
FILTER ( SECURITY, SECURITY[Email] = USERPRINCIPALNAME () )
)
)

However this still not working, if the field SECURITY[Account] is blank for a user then it still basically prevents them from seeing anything at all, again the intention here is if I leave the Account field blank then that rule should NOT apply to them.

Thanks again for all the help to date!

Regards,

[Anonymous]

This has become even more critical for me to solve if possible now as I just realised an assumption I had made is wrong on RLS... I had thought if I had 3 RLS Rulesets like the above for example called:

Account Security

And another more or less identical formula but for Country Security and I put a user into both rules then it would limit the data both ways... but it appears thats not the case and when you test as someone in both rules it completly ignores BOTH rules? In Desktop anyway.

[Anonymous]

Apologies for the bump here, really need help on this!

Thanks,

[v-yuta-msft]

@Anonymous ,

How about using dax below?

[Account] =
CALCULATETABLE (
    VALUES ( SECURITY[Account] ),
    FILTER (
        ALL ( SECURITY ),
        SECURITY[Email] = USERPRINCIPALNAME ()
            && SECURITY[Account] <> BLANK ()
    )
)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks so much for your reply,

I just tried this as

[Account]
IN CALCULATETABLE (
VALUES ( SECURITY[Account] ),
FILTER (
ALL ( SECURITY ),
SECURITY[Email] = USERPRINCIPALNAME()
&& SECURITY[Account] <> BLANK ()
)
)

and the table still stays filtered? (doesnt work as an = either sadly 😞

[vik0810]

@Anonymous , as far as I understand, your intention is not to filter at all, if the account is empty. You have to use then logical OR instead of AND.

[Account] IN
CALCULATETABLE (
    VALUES ( SECURITY[Account] ),
    FILTER (
        ALL ( SECURITY ),
        SECURITY[Email] = USERPRINCIPALNAME ()
            || ISBLANK(SECURITY[Account])
    )
)

[Anonymous]

Thanks @vik0810 

That returns nothing at all though, What I want to happen is the filter to basically leave everything UNFILTERED. 

[Anonymous]

Im starting to wonder is what im hoping unrealistic / unachievable using RLS?

[v-yuta-msft]

@Anonymous ,

Have you solved your issue by now? If you have, could you please help mark the correct answer to finish the thread? Your contribution will be much appreciated.

Regards,

Jimmy Tao

[Anonymous]

How even can I do the if to say if BLANK (or any specific defined value) then filter nothing? Google is not my friend for this answer it seems.