Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Dynamic RLS Help

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:RegionCountry:
[email protected]AppleEUROPEFRANCE
[email protected]AppleEUROPEGERMANY
[email protected] EUROPE 
[email protected]  ITALY
[email protected]   

 

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,

 

 

Need Help

11 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        vik0810
        Resolver V
        6 years ago

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

         

         

         

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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's avatar
    Anonymous
    Not applicable
    6 years ago

    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.