Configuring “all” access with dynamic row-level security

[Pushpak]

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.

[Spinhalyard]

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

[fieldname] <> ""

[V-lianl-msft · Accepted solution]

Hi @Pushpak ,

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-multipl... 

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.

[Pushpak]

@V-lianl-msft 

it worked.

Thanks

[amitchandak]

@Pushpak , 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

[Pushpak]

@amitchandak 

Can't we write DAX for this scenario ?