Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX Code for Dynamic RLS

I am new to DAX and am trying to implement some dynamic RLS. The current table of users details looks like below: User Email User Name Agent ID Level of Access Required Region Company tes...
Need Help
  • sturlaws's avatar
    sturlaws
    6 years ago

    sorry, was a bit quick there, you need to write it like this

    VAR _t =
    CALCULATETABLE (
        VALUES ( 'Access_Table'[Access_Required] ),
        FILTER ( 'Access_Table', 'Access_Table'[User_Email] = USERPRINCIPALNAME () )
    )
RETURN
    IF (
        CONTAINS ( _t, 'Access Table'[Access_Required], "National" )
            || CONTAINS ( _t, 'Access Table'[Access_Required], "Regional" ),
        'dimCompany'[Company]
            IN CALCULATETABLE (
                VALUES ( 'Access_Table'[Company] ),
                FILTER ( 'Access_Table', 'Access_Table'[User_Email] = USERPRINCIPALNAME () )
            ),
        TRUE ()
    )
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Firstly, thank you so much you've been really helpful!

I am 99% of the way there however I am having issues with the 'Regional' filter. Where currently if you are under the regional filter you can see your region e.g. USA but for all companies and can filter through other companies, instead of being able to only view your companies region.

sturlaws's avatar
sturlaws
Resident Rockstar
6 years ago

Sorry, you lost me. You want to see region but not all companies in that region? And only see your own company?

 

Could you create a mockup to show your desired outcome?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    So each company could have a number of regions under it for example:

     

    CompanyRegion
    AppleUK
    AppleUSA
    AppleCAN
    MicrosoftUK
    MicrosoftUSA
    MicrosoftCAN

     

    When a user is set up as 'Regional' it means that they should only see records that are in their region AND in their company as this will be shared externally. Does that make sense?

    sturlaws wrote:

    Sorry, you lost me. You want to see region but not all companies in that region? And only see your own company?

     

    Could you create a mockup to show your desired outcome?

     

    • sturlaws's avatar
      sturlaws
      Resident Rockstar
      6 years ago

      Now that would have been pertinent information to include in your sample data file ;)

       

      In the filter expression of dimCompany change this

      'Access Table'[Access_Required], "National"

      to

      'Access Table'[Access_Required], "National"
|| 'Access Table'[Access_Required], "Regional"

      And for dimRegion, change

      'Access Table'[Access_Required], "Regional"

      to

      'Access Table'[Access_Required], "National"

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        My apologies for missing that bit of key info out!

        When trying to change the DAX its throwing the error 'The number of arguments is invalid. Function CONTAINS must have a value for each specified column reference.?