Forum Discussion

ggipson's avatar
ggipson
Frequent Visitor
9 years ago
Solved

Role Level Security on Multiple Tables

I want to set up role level filters using 2 different tables. The default filter is to make this an 'and' filter, meaning I only want the cross sections from these two tables. I need the filters to b...
OpenDataLab's avatar
OpenDataLab
Helper II
9 years ago

 

 

  • Try something like this.  Create a table with the users and country combination

     

 

  • The set BI directional filters to your dimension tables, user and country

 

  • In your manage role, put your security filter on the bridge table

 

 By filtering the bridge table, you will propogate filter using the bi-directional relationships to your other 2 dimensions.   This should give you the OR clause that you are looking for.  

 

Remember that you need to include for John Doe a list of all the countries that he should have access to.  Not just the US.

 

Let me know how you get on.

 

 

  • ggipson's avatar
    ggipson
    Frequent Visitor
    9 years ago

    This is a great creative solution, however this does not work for me. My hierearchies currently have a relationship with a common table that has sales. Power BI will not let me create a full circle of relationships between tables due to abiguity in filtering. Please see my sample data below.

    CountryCityLocation ID
    U.S.Ney YorkA
    U.S.AtlantaB
    U.S.ChicagoC
    CanadaTorontoD
    CanadaOttawaE

     

    Sales ManagerSales PersonEmployee ID
    John DoeJane Doe1
    John DoeJanet Jackson2
    John DoeTito Jackson3
    Rick RossNick LaChey4
    Rick RossJustin Timberlake5
    Rick RossSteve Austin6

     

     

    Employee IDLocation IDAmount
    1E $          500.00
    1A $          300.00
    1B $          250.00
    2C $          750.00
    2C $          650.00
    3D $          200.00
    4D $          320.00
    5C $          890.00
    5C $          400.00
    6C $          165.00
    6E $          500.00
    6A $          230.00
    6A $          320.00
    • OpenDataLab's avatar
      OpenDataLab
      Helper II
      9 years ago

       

      You can still use the bridge table concept, the key it to ensure that you have the combination of all the users and countries that exist in your data set.

       

      Remove the BI directional joins and instead use the following code against the user table and country table under manage roles:

       

      User:

      CONTAINS (
      FILTER ( SecurityBridge, [User] = USERNAME () || [Country] = "US" ),
      'SecurityBridge'[User], 'User'[User]
      )

       

      Country

      CONTAINS (
          FILTER ( SecurityBridge, [User] = USERNAME () || [Country] = "US" ),
          'SecurityBridge'[Country], Country[Country]
      )

       

      This will filter the bridge table for a list of user that are either in the country or is that actual user.  You can then filter the user table appropriately and do the samething for the country table.  This should give you the correct combinations.

       

      Let me know how you get on.