Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Configuring “all” access with dynamic row-level security

Hi,

 

 

 

 

 

These are two tables i have used for Dynamic RLS and above DAX i have used for accessing the region but not working for region = "All". Please can anyone help me with DAX expression that will be helpful.

 

Thanks.

 

 

 

 

 

 

Need Help

5 Replies

  • Spinhalyard's avatar
    Spinhalyard
    Regular Visitor
    3 years ago

    Choose a field that will never be empty and do this for the superuser role...

    [fieldname] <> ""

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , This is what I have. I created

    A table for the region

    two columns

    Region , region with all

    northwest , northwest 

    northwest, All

     

    Mean all one entry for the region with the region  and one entry of region with all

     

    merged two tables in power query using region in security table and region with all in this new table

     

    you will get a new column with all region name

     

    that you can join with dimension and you can simply filter userprincipalname

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      amitchandak 

      Can't we write DAX for this scenario ?

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    4 years ago

    Hi Anonymous ,

     

    Based on your description, please create a column to show the CEO of these Managers.

    CEO = IF('Table'[Region]="ALL",BLANK(),CALCULATE(MIN('Table'[ID]),FILTER(ALLEXCEPT('Table','Table'[Country]),'Table'[Region]="ALL")))

    Then use PATH function to create a new column to show the whole path for the hierarchy:

    PATH = PATH('Table'[ID],'Table'[CEO])

    Finally you can use PathContains functions to check if ID exists in a path or not,then you need to add this logic as a role in the table.

    PATHCONTAINS('Table'[PATH],
MaxX(
Filter(
'Table',
[Users]=USERPRINCIPALNAME()
)
,'Table'[ID]
)
)

    Here are some helpful blogs for your reference:

    https://radacad.com/dynamic-row-level-security-in-power-bi-with-organizational-hierarchy-and-multiple-positions-in-many-to-many-relationship-part-1? 

    https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi 

     

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